We help IT Professionals succeed at work.

What is the syntax or best way to have some "IF" logic within a SELECT Query

IvanHowarth
IvanHowarth asked
on
Medium Priority
186 Views
Last Modified: 2010-03-20
Say I already have a table which has the following fields...

MyTable1
ColPK, ColClosed, ColExpired      --both ColClosed and ColExpired are bit dataTypes (i.e. True or False)
354          1               0
355          0               0
356          0               1

I now have a second table that I want to populate from MyTable1, but my second col (MyTable2.ColStatus) is a result of ColClosed and ColExpired...

MyTable2
ColPK,    ColStatus
23           Closed
24           Open
25           Expired

How do I write a query to achieve the above results?

SELECT ColPK As 'PK', (an If statement? or can I call a Procedure?) As 'Status'
FROM dbo.MyTable

A Procedure could look like:

CREATE  Procedure dbo.GetSTATUS
      @Closed bit,
      @Expired bit,
      @STATUS nvarchar(255) Output
As
If @Closed = 1
  Begin
    Set @STATUS = 'CLOSED'
  End
Else
  If @Expired = 1
    Begin
      Set @STATUS = 'EXPIRED'
    End
  Else
    If @Closed = 0 And @Expired = 0
      Begin  
        Set @STATUS = 'OPEN'
      End
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT ColPK As 'PK',  CASE WHEN ColClosed = 1 THEN 'Closed'
                                               WHEN  ColExpired = 1 THEN 'Expired'  
                                               WHEN  ColClosed = 0 AND Expired =0  THEN 'Open'  As 'Status'
FROM dbo.MyTable

Author

Commented:
I'm getting an error message:

Incorrect syntax near the keyword 'As'.
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
ooops missed an 'END'


SELECT ColPK As 'PK',  CASE WHEN ColClosed = 1 THEN 'Closed'
                                               WHEN  ColExpired = 1 THEN 'Expired'  
                                               WHEN  ColClosed = 0 AND Expired =0  THEN 'Open'  END  As 'Status'
FROM dbo.MyTable

Commented:
No points here, but what happens when ColClosed = 1 AND ColExpired = 1?
--
JimFive

Author

Commented:
Just checked out the CASE statement in Help - END was missing. Should read...

 SELECT ColPK As 'PK',  CASE WHEN ColClosed = 1 THEN 'Closed'
                                               WHEN  ColExpired = 1 THEN 'Expired'  
                                               WHEN  ColClosed = 0 AND Expired =0  THEN 'Open' END As 'Status'
FROM dbo.MyTable

Now works ok! Thanks.

Author

Commented:
JimFive: lol, the situation hasn't and wouldn't arise. Nevertheless, I'll include the possibility so it will return Expired :-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.