[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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

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
0
IvanHowarth
Asked:
IvanHowarth
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
 
IvanHowarthAuthor Commented:
I'm getting an error message:

Incorrect syntax near the keyword 'As'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
JimFiveCommented:
No points here, but what happens when ColClosed = 1 AND ColExpired = 1?
--
JimFive
0
 
IvanHowarthAuthor 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.
0
 
IvanHowarthAuthor Commented:
JimFive: lol, the situation hasn't and wouldn't arise. Nevertheless, I'll include the possibility so it will return Expired :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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