• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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
0
shmz
Asked:
shmz
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Christopher GordonSenior 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
 
_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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now