SQL: Return Expressions from SQL CASE statement

Question

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.

Reason being, `THEN` clause is for returning a value, not for comparing the values. You can't use any operators withe the values being returned in the `THEN` clause — Incognito, Feb 04 '14 at 10:55
No, it isn't possible. You're trying to return the expression as part of the result set which doesn't make sense; what would you do with the result? Do you actually want to return either `##0` or the value of `NVL(IL.ECL, 'X')`, which would be fine? — Alex Poole, Feb 04 '14 at 10:56
The ECL_CONTROL column contain flag value 'Y' or 'N', if the value is 'Y' i want to return `NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X')` if the value is 'N' the return value is `('##0') = ('##0')` . This query is act as a inner query and in inner query these return statements return an expression for where condition. — Harmeet Singh Taara, Feb 04 '14 at 11:01
`IL` and `IM` is the alias or outer query tables. if the flag is 'N' so ,we need only `true` expression in outer query `Y`, then i need to add another expression. — Harmeet Singh Taara, Feb 04 '14 at 11:07

Alex Poole · Answer 1 · 2014-02-04T11:39:33.580

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')?

sorry for the `IL` and `IM` confusing , actual these are the alias of outer query tables. — Harmeet Singh Taara, Feb 04 '14 at 11:09
@HarmeetSingh - the answer to your question is simply 'no'; if you want help achieving your wider goal you'll need to provide more context in the question, showing how the expression would be used. Though I think what I've shown might point you in the right direction. `case` might not be the right tool here. But without the context of the rest of the query I'm guessing. — Alex Poole, Feb 04 '14 at 11:12
thanks for giving me the solution, but my `ITEM_NO` is not fixed and its comes from outer query and when we use `((SELECT ECL_CONTROL FROM ITEM_CONTROL WHERE ITEM_NO = '2N2907A') = 'N' OR NVL(IL.ECL, 'X') = NVL(IM.ECL, 'X'))` it contains both expression, . But i need only one expression depends on `Y` or `N` value in outer query because if the values is `N` there is no need for this expression. thanks again alex. — Harmeet Singh Taara, Feb 04 '14 at 11:35
Hey @Alex thanks for this, But i want to confirm one think, in oracle 10g sql, the `OR` operator is Short Circuit ? — Harmeet Singh Taara, Feb 04 '14 at 11:45
@HarmeetSingh - well, yes (although I can only see it explicitly stated [for PL/SQL](http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CIHGJBFD) but it doesn't affect the result here, and doesn't involve more work since the second expression doesn't involve any calculation. I suppose you're sometimes skipping the retrieval of `IM.ECL` but that would only make any difference at all if it was in a different data block to the other `IM` columns you're using. — Alex Poole, Feb 04 '14 at 11:52
@HarmeetSingh - actually... [no, it isn't guaranteed to for SQL](http://stackoverflow.com/a/8900752/266304). The optimiser decides what to evaluate when. But it still doesn't make any practical difference in this case as there are no side-effects. — Alex Poole, Feb 04 '14 at 12:32

SQL: Return Expressions from SQL CASE statement

1 Answers1