In clause and variables within CASE statement 1

[SQLHacker]

I'm using Oracle version 10.2 and have a query which contains many WHERE conditions, but one in particular I cannot get to work. Here is the code:

where table.division_id in (CASE WHEN :Division = 'AA' THEN '02'
				 WHEN :Division = 'BB' THEN '09'
				 WHEN :Division = 'CC' THEN '07'
				 WHEN :Division = 'ALL' THEN '02'||','||'07'||','||'09' 
			    END CASE)

If I comment out the last line, and move the END CASE statement to follow the 'CC" line, it works just fine. I have tried MANY different ways to concatonate the values together, but none of them work. Any helpful suggestions would be greatly appreciated.

 

[Dagon]

You can't do IN statements this way, I'm afraid. For something like that you'd have to use dynamic SQL. Oracle wouldn't parse the '02'||','||'07'||','||'09' as a list and the best you'd ever manage would be to get it comparing ivision to the string '02,07,09', which isn't what you want.

Retired (not by choice) Oracle contractor.

 

[zephan]

should try to use something (not very elegant like) like

where table.division_id in (
SELECT '02' FROM dual where :Division = 'AA' OR :Division = 'ALL'
UNION
SELECT '09' FROM dual where :Division = 'BB' OR :Division = 'ALL'
UNION
SELECT '07' FROM dual where :Division = 'CC' OR :Division = 'ALL'
          )

 

[SantaMufasa]

Zephan,

Since Oracle's "IN" operator is problematic in your situation, you can use Oracle's INSTR operator instead:

select * from tabl
where INSTR((CASE WHEN :Division = 'AA' THEN '02'
                  WHEN :Division = 'BB' THEN '09'
                  WHEN :Division = 'CC' THEN '07'
                  WHEN :Division = 'ALL' THEN '02,07,09'
             END),DIVISION_ID)>0
/

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

I think that was a terrific suggestion and worthy of a star. Since not many of the original posters seem to want to express any thanks at the moment, I shall award one.

Retired (not by choice) Oracle contractor.

 

[SantaMufasa]

Thanks, Dagon...you are very thoughtful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”