CODEWITHSUNDEEP
sqldatabaseoracle10gcase
Harmeet Singh Taara
Harmeet Singh Taara

Reputation: 6611

SQL: Return Expressions from SQL CASE statement

Thanks in Advance, i use an oracle 10g. In SQL case statement we pass the value and check the case according to value and return string. But instead of returning String, i want to return an expression. Is this is possible using oracle SQL query CASE statement for returning an expression? I do the following and oracle generate an error ORA-00905:Missing Keyword

SELECT ECL_CONTROL,
  CASE 
    WHEN ECL_CONTROL = 'N' THEN ('##0') = ('##0')
    WHEN ECL_CONTROL = 'Y' THEN (NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X'))
  END
FROM ITEM_CONTROL WHERE ITEM_NO =  '2N2907A'

But The following run successfully : 

SELECT ECL_CONTROL,
  CASE 
    WHEN ECL_CONTROL = 'N' THEN 'test'
    WHEN ECL_CONTROL = 'Y' THEN 'demo'
  END
FROM ITEM_CONTROL WHERE ITEM_NO =  '2N2907A'

I want to use this query as the inner query and return and expression for outer query. My query is following : 

SELECT  LSM.ACTIVE_FLAG, LSM.ITEM_NO, IL.ECL, LSM.SERIAL_NO, IL.WAREHOUSE, IL.BIN, LSM.TOOL_STATUS_CODE,
LLG.NEXT_CAL_DATE, IM.PRODUCT_GROUP
FROM LS_SERIAL_MASTER LSM, LS_LIFE_LOG LLG, ITEM_LOCATION IL, ITEM_MASTER IM
WHERE   IL.PLANT = IM.PLANT AND IL.ITEM_NO = IM.ITEM_NO 
AND IM.PLANT = LSM.PLANT AND IM.ITEM_NO = LSM.ITEM_NO AND NVL(IM.PRODUCT_GROUP, 'X') =    NVL('PUMP', 'X')
AND LSM.PLANT = LLG.PLANT AND LSM.ITEM_NO = LLG.ITEM_NO AND LSM.SERIAL_NO = LLG.SERIAL_NO
AND LSM.LIFE_TRANS_ID = LLG.TRANS_ID AND LLG.SERIAL_NO = IL.SERIAL_NO
AND (SELECT ECL_CONTROL,
  CASE ECL_CONTROL
    WHEN ECL_CONTROL = 'N' THEN ('##0' = '##0')
    WHEN ECL_CONTROL = 'Y' THEN (NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X'))
  END AS ECL_CONTROL_TXT
  FROM ITEM_CONTROL WHERE ITEM_NO =  '2N2907A')
AND LLG.MAINT_LMT_ID IN('T', 'U')
AND LLG.NEXT_CAL_DATE BETWEEN TO_DATE('02/1/2014', 'MM/DD/YYYY') AND TO_DATE('02/11/2014', 'MM/DD/YYYY').

My actual task is return an expression from inner query and used in outer query.

Upvotes: 1

Views: 2209

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

No, what you've shown isn't possible. It doesn't make sense to have an expression in the select really. You can have an expression evaluated instead of using a fixed string:

SELECT ECL_CONTROL,
  CASE 
    WHEN ECL_CONTROL = 'N' THEN '##0'
    WHEN ECL_CONTROL = 'Y' THEN NVL(IL.ECL, 'X')
  END
FROM ITEM_CONTROL WHERE ITEM_NO =  '2N2907A'

But you can't return the actual expression. In the code you've shown the first expression would always evaluate to true anyway (and booleans don't exist in plain SQL either), so I think this is maybe what you actually intended and you're confused about how to assign the expression value. You haven't shown where IL and IM come from so it's hard to tell quite what you want.

You can use a case directly in a where clause:

SELECT ...
FROM IM, IL, ITEM_CONTROL -- joined in some way
WHERE CASE 
    WHEN ECL_CONTROL = 'N' THEN '##0'
    WHEN ECL_CONTROL = 'Y' THEN NVL(IL.ECL, 'X')
  END = CASE 
    WHEN ECL_CONTROL = 'N' THEN '##0'
    WHEN ECL_CONTROL = 'Y' THEN NVL(IM.ECL, 'X')
  END

Though that might be clearer as:

WHERE ECL_CONTROL = 'N' OR NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X')

From the larger query you added:

SELECT LSM.ACTIVE_FLAG, LSM.ITEM_NO, IL.ECL, LSM.SERIAL_NO, IL.WAREHOUSE,
  IL.BIN, LSM.TOOL_STATUS_CODE, LLG.NEXT_CAL_DATE, IM.PRODUCT_GROUP
FROM LS_SERIAL_MASTER LSM, LS_LIFE_LOG LLG, ITEM_LOCATION IL, ITEM_MASTER IM
WHERE IL.PLANT = IM.PLANT AND IL.ITEM_NO = IM.ITEM_NO 
AND IM.PLANT = LSM.PLANT AND IM.ITEM_NO = LSM.ITEM_NO
AND NVL(IM.PRODUCT_GROUP, 'X') = NVL('PUMP', 'X')
AND LSM.PLANT = LLG.PLANT AND LSM.ITEM_NO = LLG.ITEM_NO
AND LSM.SERIAL_NO = LLG.SERIAL_NO
AND LSM.LIFE_TRANS_ID = LLG.TRANS_ID AND LLG.SERIAL_NO = IL.SERIAL_NO
AND LLG.MAINT_LMT_ID IN('T', 'U')
AND LLG.NEXT_CAL_DATE BETWEEN TO_DATE('02/1/2014', 'MM/DD/YYYY')
  AND TO_DATE('02/11/2014', 'MM/DD/YYYY')
AND ((SELECT ECL_CONTROL FROM ITEM_CONTROL WHERE ITEM_NO = '2N2907A') = 'N'
  OR NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X'))

... though since the subquery is looking for a single row - with a fixed item_no that has relation to the other columns with the same name you reference in other tables - you could just include that table in the main query.

Either way. this would be clearer with explicit joins, incidentally; rather outside the scope of the question, but taking on your comment that the item_no is actually related, something like:

SELECT LSM.ACTIVE_FLAG, LSM.ITEM_NO, IL.ECL, LSM.SERIAL_NO, IL.WAREHOUSE,
  IL.BIN, LSM.TOOL_STATUS_CODE, LLG.NEXT_CAL_DATE, IM.PRODUCT_GROUP
FROM LS_LIFE_LOG LLG
JOIN LS_SERIAL_MASTER LSM ON LSM.PLANT = LLG.PLANT
  AND LSM.ITEM_NO = LLG.ITEM_NO
  AND LSM.SERIAL_NO = LLG.SERIAL_NO
  AND LSM.LIFE_TRANS_ID = LLG.TRANS_ID
JOIN ITEM_CONTROL IC ON IC.ITEM_NO = LSM.ITEM_NO
JOIN ITEM_MASTER IM ON IM.PLANT = LSM.PLANT
  AND IM.ITEM_NO = IC.ITEM_NO
JOIN ITEM_LOCATION IL ON IL.PLANT = IM.PLANT
  AND IL.ITEM_NO = IM.ITEM_NO
  AND IL.SERIAL_NO = LLG.SERIAL_NO
  AND (IC.ECL_CONTROL = 'N' OR NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X'))
WHERE LLG.MAINT_LMT_ID IN ('T', 'U')
AND LLG.NEXT_CAL_DATE BETWEEN TO_DATE('02/1/2014', 'MM/DD/YYYY')
  AND TO_DATE('02/11/2014', 'MM/DD/YYYY')
AND NVL(IM.PRODUCT_GROUP, 'X') = NVL('PUMP', 'X')

NVL('PUMP', 'X') doesn't make sense though - the NVL() is pointless around a fixed value. Maybe instead of AND NVL(IM.PRODUCT_GROUP, 'X') = NVL('PUMP', 'X') you mean AND (IM.PRODUCT_GROUP IS NULL OR IM.PRODUCT_GROUP = 'PUMP')?

Upvotes: 1

Related Questions