Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

nested case statement assistance 1

Status
Not open for further replies.
catchingup

catchingup

Technical User
May 11, 2006
37
US
I am trying to use a nested case statement and am having trouble with the info displaying in the newly created field.

Using the statement below, I am able to run the query, but the information that populates in the new field 'UCFLAG' is incorrect.
It assigns a value of 'NA' to every row in the table,
Any ideas on how to correct this? Any help is much appreciated!!

SELECT #tempComm.Name, #tempComm.Date,
#tempComm.Recd, #tempComm.Cont,

CASE #tempComm.Recd WHEN NULL
THEN (CASE #tempComm.Cont WHEN 'Sub' THEN 'OK'
WHEN 'PO2' THEN 'OK'
WHEN 'Job' THEN 'FLAG'
WHEN 'Open' THEN 'FLAG'
ELSE 'NONE' END)
ELSE 'NA' END AS UCFLAG

FROM #tempComm
 
Sort by date Sort by votes
When testing for null you need to use the other format of CASE. Like this:

Code: 
SELECT #tempComm.Name,  #tempComm.Date,
    #tempComm.Recd, #tempComm.Cont,

CASE WHEN #tempComm.Recd IS NULL  
   THEN (CASE #tempComm.Cont WHEN 'Sub' THEN 'OK'
                 WHEN 'PO2' THEN 'OK'
                 WHEN 'Job' THEN 'FLAG'
                 WHEN 'Open' THEN 'FLAG' 
                 ELSE 'NONE' END)
    ELSE 'NA' END AS UCFLAG

FROM   #tempComm

Hope it helps, and that you see the subtle change that was made.

ALex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Glad to hear it :)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I'm using this further down as well and getting an error indicating Incorrect syntax near the keyword 'AS' (the final 'as' is the one they are referring to. Any ideas why?

CASE WHEN #tempComm.WC IS NULL
THEN 'FLAG'
WHEN #tempComm.WC IS NOT NULL
THEN (CASE WHEN #tempComm.Aut IS NULL
THEN 'FLAG'
WHEN #tempComm.WC IS NOT NULL
THEN (CASE WHEN #tempComm.GL IS NULL
THEN 'FLAG'
WHEN #tempComm.GL_Exp IS NOT NULL
THEN (CASE WHEN #tempComm.Umb IS NULL
THEN 'FLAG'
WHEN #tempComm.Umb IS NOT NULL
THEN 'OK' END)
ELSE 'NONE' END AS INFLAG,
 
Upvote 0 Downvote
Although it doesn't always seem that way, every CASE must have an END. Try this, I think it is what you're after.

Code: 
CASE  WHEN #tempComm.WC IS NULL
    THEN 'FLAG'
    WHEN #tempComm.WC IS NOT NULL
    THEN (CASE WHEN #tempComm.Aut IS NULL
    THEN 'FLAG' 
    WHEN #tempComm.WC IS NOT NULL
    THEN (CASE WHEN #tempComm.GL IS NULL
    THEN 'FLAG'
    WHEN #tempComm.GL_Exp IS NOT NULL 
    THEN (CASE WHEN #tempComm.Umb IS NULL
    THEN 'FLAG'
    WHEN #tempComm.Umb IS NOT NULL
    THEN 'OK' END)[b]END)END)[/b] 
    ELSE 'NONE' END AS INFLAG

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
120
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
461
mikrom
mikrom
ms901Boss
  • Locked
  • Question
Joining two select statement into one
Replies
1
Views
82
kray4660
kray4660
Bomac8
  • Locked
  • Question
Convert IIF with switch statement to Case
Replies
3
Views
106
dhookom
dhookom
raven4
  • Locked
  • Question
Nested IF Statement
Replies
7
Views
102
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top