case with in case statement ?

Question

hi is there a way to write a case statement with in a case statement ?like below i know the below one is syntactical wrong..but just to give an idea 

I need the output in 1, 2,3, 4 ,5 ( integer values)

SELECT Status, 
CASE
WHEN [STATUS] = 'Not Received'   THEN 'Not Received'

  (CASE WHEN [STATUS] = 'Not Received'  THEN 1 )

WHEN [STATUS] = 'In Review'      THEN 'In Review'

  (CASE WHEN [STATUS] = 'In Review'      THEN  2)

WHEN [STATUS] = 'Not Conducted'  THEN 'Not Conducted'

   (CASE WHEN [STATUS] = 'Not Conducted'  THEN 3)

WHEN [STATUS] = 'Not Conducted'  THEN ' Conducted'

   (CASE WHEN [STATUS] = 'Conducted'  THEN 4)

WHEN  D.Date IS NOT NULL THEN 'Completed'

  (CASE WHEN [STATUS] = 'Completed'  THEN )

  WHEN  D.RDate IS NOT NULL THEN 'In Review'

  (CASE WHEN [STATUS] = 'In Review'      THEN  2)

my original case statemente is 

SELECT 
CASE
WHEN [STATUS] = 'Not Received'   THEN 'Not Received'
WHEN [STATUS] = 'In Review'      THEN 'In Review'
WHEN [STATUS] = 'Not Conducted'  THEN 'Not Conducted'
WHEN [STATUS] = 'Conditional'    THEN 'Conditional'
WHEN [STATUS] = 'Completed'      THEN 'Completed'
WHEN [STATUS] = 'Unknown'        THEN 'NULL'
WHEN  D.AcceptedDate IS NOT NULL THEN 'Completed'
WHEN  D.ReceivedDate IS NOT NULL THEN 'In Review'
ELSE NULL
END AS STATUS

ELSE NULL
END AS STATUS

Please help me ...

Thanks

Answer 1

I'm not sure exactly what you want, but perhaps something like

SELECT Status, 
CASE
WHEN [STATUS] = 'Not Received'   THEN 1
WHEN [STATUS] = 'In Review'      THEN 2
WHEN [STATUS] = 'Not Conducted'  THEN 3
WHEN [STATUS] = 'Conducted'  THEN 4
WHEN  D.Date IS NOT NULL THEN THEN 1
WHEN  D.RDate IS NOT NULL THEN  2
END

It would be helpful if you gave some sample data and the result you want from the sample data.

Tom

Answer 2

Yes.  You could do this:

SELECT 
CASE (
CASE
WHEN [STATUS] = 'Not Received'   THEN 'Not Received'
WHEN [STATUS] = 'In Review'      THEN 'In Review'
WHEN [STATUS] = 'Not Conducted'  THEN 'Not Conducted'
WHEN [STATUS] = 'Conditional'    THEN 'Conditional'
WHEN [STATUS] = 'Completed'      THEN 'Completed'
WHEN [STATUS] = 'Unknown'        THEN 'NULL'
WHEN  D.AcceptedDate IS NOT NULL THEN 'Completed'
WHEN  D.ReceivedDate IS NOT NULL THEN 'In Review'
ELSE NULL
END )
WHEN 'Not Received' THEN 1
WHEN 'In Review' THEN 2
WHEN 'Not Conducted' THEN 3
ELSE NULL
END AS STATUS 

Answer 3

ok, thanks for the reply 

actually I am taking data from X table and inserting to a y table  and i have a z table ( lookup table)

X table status is Varchar and i have to insert to y table ststusID  in integer 

so ,

Select CASE

WHEN [STATUS] = 'Not Received'   THEN 'Not Received'
WHEN [STATUS] = 'In Review'      THEN 'In Review'
WHEN [STATUS] = 'Not Conducted'  THEN 'Not Conducted'
WHEN [STATUS] = 'Conditional'    THEN 'Conditional'
WHEN [STATUS] = 'Completed'      THEN 'Completed'
WHEN [STATUS] = 'Unknown'        THEN 'NULL'
WHEN  D.AcceptedDate IS NOT NULL THEN 'Completed'
WHEN  D.ReceivedDate IS NOT NULL THEN 'In Review'
ELSE NULL
END AS STATUS

for this i have to do

SELECT Status, 
CASE
WHEN [STATUS] = 'Not Received'   THEN 'Not Received'

  (CASE WHEN [STATUS] = 'Not Received'  THEN 1 )

WHEN [STATUS] = 'In Review'      THEN 'In Review'

  (CASE WHEN [STATUS] = 'In Review'      THEN  2)

WHEN [STATUS] = 'Not Conducted'  THEN 'Not Conducted'

   (CASE WHEN [STATUS] = 'Not Conducted'  THEN 3)

WHEN [STATUS] = 'Not Conducted'  THEN ' Conducted'

   (CASE WHEN [STATUS] = 'Conducted'  THEN 4)

WHEN  D.Date IS NOT NULL THEN 'Completed'

  (CASE WHEN [STATUS] = 'Completed'  THEN )

  WHEN  D.RDate IS NOT NULL THEN 'In Review'

  (CASE WHEN [STATUS] = 'In Review'      THEN  2)

please help me on this

Thanks

Answer 4

,CASE
WHEN [STATUS] = 'Not Received'   THEN 1 ----'Not Received'
WHEN [STATUS] = 'In Review'      THEN 2 --- 'In Review'
WHEN [STATUS] = 'Not Conducted'  THEN 3---- 'Not Conducted'
WHEN [STATUS] = 'Conditional'    THEN 4--- 'Conditional'
WHEN [STATUS] = 'Completed'      THEN 5--- 'Completed'
WHEN [STATUS] = 'Unknown'        THEN NULL
WHEN  D.AcceptedDate IS NOT NULL THEN 5---'Completed'
WHEN  D.ReceivedDate IS NOT NULL THEN 2---'In Review'
ELSE NULL
END AS STATUS

got the answer

   

Answer 5

This is a case expression, not a statement! The WHEN Clauses are check in the order written.

CASE
WHEN something_status = 'Not Received' 
  OR receipt_date IS NULL
THEN 1
WHEN something_status = 'In Review' 
  OR review_date IS NOT NULL
THEN 2
WHEN something_status = 'Not Conducted' THEN 3
WHEN something_status = 'Conducted' THEN 4
END AS something_status 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL