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
shmzAsked:
Who is Participating?
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
SELECT cast (case count(1) when 0 then 1 else 0 end as bit) ColumnA From TableX

If TableX has data then return 0 (i.e when number of rows is 0 hen return true)

If TableX has no data return 1 (i.e. when number of rows is not 0 (then return false)



0
 
hackpinCommented:
well it the query uses count to check if there are records in table, you can use count(field name).
0
 
Christopher GordonSenior Developer AnalystCommented:
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).
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
shmzAuthor Commented:
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....
0
 
_DJCommented:
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
0
 
rmm2001Commented:
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.
0
 
shmzAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.