?
Solved

SQL Select Case

Posted on 2009-04-30
5
Medium Priority
?
1,810 Views
Last Modified: 2012-06-21
Hello.

I am trying to create a case in my sql statement.

The case should be. (IsForSaleButNoOptions )

SELECT * from table1 IF THERE are no records then 'False' Else 'True'

What am I doing wrong. Is the select statement not allowed?
SELECT *, 
 
IsForSale = CASE WHEN pfs.gallery_Id IS NULL THEN 'False' ELSE 'True' END, 
 
IsForSaleButNoOptions = CASE ISNULL(SELECT * FROM Gower_tbl_photosforsaleoptions, '0') WHEN '0' THEN 'True' ELSE 'False' END 
 
 
 
 
 
 
FROM Gower_tbl_gallery g LEFT JOIN Gower_tbl_images i ON g.image_id = i.image_id 
LEFT JOIN Gower_tbl_photosforsale pfs ON g.gallery_Id = pfs.gallery_Id 
/*LEFT JOIN Gower_tbl_photosforsaleoptions pfso ON pfs.pfs_Id = pfso.pfs_Id */
WHERE i.UserId = 'c92adece-ef95-4c72-8057-8185dc9a0983' 
ORDER BY i.image_dateadded DESC

Open in new window

0
Comment
Question by:thomasmutton
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24267707
this should work better:
IsForSaleButNoOptions = CASE WHEN EXISTS(SELECT * FROM Gower_tbl_photosforsaleoptions) THEN 'True' ELSE 'False' END 

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24267709
Hi thomasmutton,

The bits like "IsForSale = " is not valid ... you can alias the CASE statement like anyother value ... e.g.

lwadwell
SELECT *, 
CASE WHEN pfs.gallery_Id IS NULL THEN 'False' ELSE 'True' END  as IsForSale, 
CASE ISNULL(SELECT * FROM Gower_tbl_photosforsaleoptions, '0') WHEN '0' THEN 'True' ELSE 'False' END as IsForSaleButNoOptions
...

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267711
try this.
SELECT *, 
 
IsForSale = CASE WHEN pfs.gallery_Id IS NULL THEN 'False' ELSE 'True' END, 
 
IsForSaleButNoOptions = CASE ISNULL(SELECT count(*) FROM Gower_tbl_photosforsaleoptions, '0') WHEN '0' THEN 'True' ELSE 'False' END 
 
 
 
 
 
 
FROM Gower_tbl_gallery g LEFT JOIN Gower_tbl_images i ON g.image_id = i.image_id 
LEFT JOIN Gower_tbl_photosforsale pfs ON g.gallery_Id = pfs.gallery_Id 
/*LEFT JOIN Gower_tbl_photosforsaleoptions pfso ON pfs.pfs_Id = pfso.pfs_Id */
WHERE i.UserId = 'c92adece-ef95-4c72-8057-8185dc9a0983' 
ORDER BY i.image_dateadded DESC

Open in new window

0
 

Author Closing Comment

by:thomasmutton
ID: 31576325
worked great.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24267819
note:

IsForSaleButNoOptions = CASE ISNULL(SELECT * FROM Gower_tbl_photosforsaleoptions, '0') ....

might also work like this (another couple of () around the select)
IsForSaleButNoOptions = CASE ISNULL((SELECT COUNT(*) FROM Gower_tbl_photosforsaleoptions ), 0)

0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question