sql query SQL 2005
HI,
I have a repor to fix and somebody has used this:
SELECT cast (case count(1) when 0 then 1 else 0 end as bit) ColumnA From TableX
totally intrigued why?
ColumnA returns either 0 or 1. Why count(1) is used and why 0 or 1?
Thanks
I have a repor to fix and somebody has used this:
SELECT cast (case count(1) when 0 then 1 else 0 end as bit) ColumnA From TableX
totally intrigued why?
ColumnA returns either 0 or 1. Why count(1) is used and why 0 or 1?
Thanks
well it the query uses count to check if there are records in table, you can use count(field name).
heh, I agree strange syntax. You usually see count(*) used to represent what the author is accomplishing with that query.
All that is doing is counting the number of records in the result set. You could really replace that with 0, or 1, or 2, or 'Eat at Joes' for that matter.
Count(*) is how i'd of wrote it instead of count(1).
All that is doing is counting the number of records in the result set. You could really replace that with 0, or 1, or 2, or 'Eat at Joes' for that matter.
Count(*) is how i'd of wrote it instead of count(1).
ASKER
well, it seems that it turn to be 'true' or false when I run it in SQL server 2005 Report.
And then inside the report it checks to display a table in the report based on this value:
IIF (First(ColumnA.Value), "dataset1"), False, True)
I don't understand why if the result is 0 then 1 else 0....
And then inside the report it checks to display a table in the report based on this value:
IIF (First(ColumnA.Value), "dataset1"), False, True)
I don't understand why if the result is 0 then 1 else 0....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
count(1) Will return number of records selected from TableX.
CASE WHEN works as if else so if count(1) is 0 then it will return 1 else 0
and cast will convert it in true and false
CASE WHEN works as if else so if count(1) is 0 then it will return 1 else 0
and cast will convert it in true and false
Rewrite it..
SELECT cast (case count(*) when 0 then 1 else 0 end as bit) ColumnA From TableX
So SELECT COUNT(*) FROM Table. If COUNT(*) = 0 then return true (a 1). If its <> 0 then return false (a 0)
If the first value in ColumnA = "dataset1" then report back false. Else it's true.
SELECT cast (case count(*) when 0 then 1 else 0 end as bit) ColumnA From TableX
So SELECT COUNT(*) FROM Table. If COUNT(*) = 0 then return true (a 1). If its <> 0 then return false (a 0)
If the first value in ColumnA = "dataset1" then report back false. Else it's true.
ASKER
Thanks