Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-06-21 : 06:24:02

Hi,

I have the following statement;

SELECT     GPRDTech.gprdsql.TblPracDetails.prac_no, GPRDTech.gprdsql.TblPracDetails.practice_name, GPRDTech.gprdsql.TblPracDetails.prac_status, 
                      CASE WHEN GPRDTech.gprdsql.TblPracDetails.prac_status = 'old' THEN 'False' ELSE COALESCE (dbo.TblPracExclude.prac_enabled, 'True') 
                      END AS prac_enabled
FROM         GPRDTech.gprdsql.TblPracDetails LEFT OUTER JOIN
                      dbo.TblPracExclude ON GPRDTech.gprdsql.TblPracDetails.prac_no = dbo.TblPracExclude.prac_no

Results

prac_no prac_name prac_status prac_enabled
1 A Old False
2 B Active False (update manually)
3 C Active True
4 D Active False

For the last record,

It should be

prac_no prac_name prac_status prac_enabled
4 D Active True

Mainly because, prac_enabled was NULL so the select statement was updated that if a prac_no has a NULL prac_enabled it should be TRUE as default. ALL prac_no with prac_status = OLD should be FALSE.

Why is it showing the last record with a FALSE value though?

Thank you

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 06:31:48

The only way it can be false is if
GPRDTech.gprdsql.TblPracDetails.prac_status = 'old'
If that is true it dodesn't matter what is in dbo.TblPracExclude.prac_enabled

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-21 : 06:34:33

check dbo.TblPracExclude.prac_enabled column for that record. See does it has a value 'False'

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources