1

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.

Improve this question
4
  • 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 Commented Feb 4, 2014 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? Commented Feb 4, 2014 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. Commented Feb 4, 2014 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. Commented Feb 4, 2014 at 11:07

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

sorry for the IL and IM confusing , actual these are the alias of outer query tables.
@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.
OK , @Alex Poole, i describe my question.
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.
Hey @Alex thanks for this, But i want to confirm one think, in oracle 10g sql, the OR operator is Short Circuit ?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.