CASE with IN clause

Question

I have a condition (in Business objects ) as below

=If([Actual ]=0;Sum([Applied] In ([Project];[ Name];[Number];[Sub ])))

which I need to convert it to CASE statement for my OBIEE

Below is the query I had tried but it doesn't work:

SELECT
    CASE 
    WHEN F.ACTUAL_EQP_COST = 0 
        THEN SUM((F.HRS_APPLIED) IN(F.PROJECT,F.NAME,F.NUMBER,F.SUB)) 
    ELSE 0 
    END 
FROM  F

Try editing your question with sample data and desired results. The query makes no sense. — Gordon Linoff, Jan 30 '17 at 12:37
What does this "Sum([Applied] In ([Project];[ Name];[Number];[Sub ])" mean? — Marcin Wroblewski, Jan 30 '17 at 12:41
I believe those inside IN () are column names .... seems like they are flags or something like that — Veljko89, Jan 30 '17 at 12:47
Translate the BO statement to English first. Then you'll have an easily understood requirement for your sql. — Dan Bracuk, Jan 30 '17 at 12:52
"it doesn't work" <-- did you get an error ? Did it run but you got the wrong answer? — Robin Moffatt, Jan 31 '17 at 23:14

score 1 · Answer 1 · answered Jan 30 '17 at 12:41

1

Probably

select SUM(F.HRS_APPLIED) 
from DTS_OSC_WIP_REP_CST_ANALYSIS_A F
where F.ACTUAL_EQP_COST = 0 
group by F.PROJECT,F.WIP_ENTITY_NAME,F.OPERATION_NUMBER,F.OPERATION_SUB_SEQ

answered Jan 30 '17 at 12:41

Serg

22,285
5
21
48

score 0 · Answer 2 · answered Jan 30 '17 at 12:45

You can't use "in" inside a "then" statement. I couldn't understand exactly but something in this direction might help:

select 
CASE WHEN F.ACTUAL_EQP_COST = 0 
THEN (SELECT SUM(F1.HRS_APPLIED) 
FROM DTS_OSC_WIP_REP_CST_ANALYSIS_A F1 
WHERE F1.column_name IN (F.PROJECT,F.WIP_ENTITY_NAME,F.OPERATION_NUMBER,F.OPERATION_SUB_SEQ)) 
ELSE 0 END 
from DTS_OSC_WIP_REP_CST_ANALYSIS_A F

CASE with IN clause

2 Answers2