CODEWITHSUNDEEP
sqloracle-database
JUANLUISSG
JUANLUISSG

Reputation: 63

using IN a where using a case statement

I am trying to create this query for a report the '&BO_STATUS' = 'AAA' is a value that the user needs to input. When the value is AAA I want to retrieve all the BO with the status of In Progress and Confirmed. For some reason, I keep getting an error. I have been trying to do this in different ways. 

SELECT T1.OPPORTUNITY_NO AS BO_NUMBER,
T1.SOURCE_ID_DB AS SOURCE_ID, 
T1.OPPORTUNITY_TYPE,
T1.STATE AS Status
FROM BUSINESS_OPPORTUNITY T1
WHERE
T1.STATE IN
(CASE
  WHEN '&BO_Status' = 'AAA' THEN '''In Progress','Confirmed'''
  END
)

Upvotes: 0

Views: 58

Answers (3)

Popeye
Popeye

Reputation: 35900

If you are explicitly told to use case..when statement then you should use following:

WHERE case when '&BO_Status' = 'AAA' 
           Then case when T1.STATE IN ('In Progress', 'Confirmed')
                Then 1
                End
           Else 1
           End = 1

Or else best way of doing it is not to use case..when statement at all as suggested by @gordon.

WHERE ('&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')) OR
      '&BO_Status' <> 'AAA'

I am considering that when status is not AAA then you want everyting (no where condition)

Cheers!!

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

You can maximize the use of INSTR function for this scenario. Specially when you are trying to build your string conditions.

SELECT T1.OPPORTUNITY_NO AS BO_NUMBER,
T1.SOURCE_ID_DB AS SOURCE_ID, 
T1.OPPORTUNITY_TYPE,
T1.STATE AS Status
FROM BUSINESS_OPPORTUNITY T1
WHERE INSTR('In Progress, Confirmed', T1.STATE, 1, 1) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Don't use a case EXPRESSION. Just use regular logic:

WHERE '&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')

I imagine that you also want an "else" so the query returns something if the condition is not true. If so, then you want:

WHERE ('&BO_Status' = 'AAA' AND T1.STATE IN ('In Progress', 'Confirmed')) OR
      '&BO_Status' <> 'AAA'

Upvotes: 3

Related Questions