Home » SQL & PL/SQL » SQL & PL/SQL » Passing variable values within a CASE statement (Oracle (using TOAD), 10.2.0, Windows)
Passing variable values within a CASE statement [message #441676] Tue, 02 February 2010 10:33 Go to next message
SQL_Hacker
Messages: 5
Registered: February 2010
Junior Member
I am having problems with a query that has many WHERE conditions, and one of them is the following:
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 simply use the first three conditions of the CASE statement, the query returns the expected results. I have tried many different ways of concatenating the last line values, and none of them have worked. Any help will be much appreciated.
Re: Passing variable values within a CASE statement [message #441682 is a reply to message #441676] Tue, 02 February 2010 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the following thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Regards
Michel
Re: Passing variable values within a CASE statement [message #441684 is a reply to message #441676] Tue, 02 February 2010 10:59 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Or, do not cope with strings at all and use collection directly.
This thread on dbForums might help you: http://www.dbforums.com/oracle/1652068-using-decode-return-multiple-strings.html#post6442304
Re: Passing variable values within a CASE statement [message #441686 is a reply to message #441682] Tue, 02 February 2010 11:25 Go to previous messageGo to next message
SQL_Hacker
Messages: 5
Registered: February 2010
Junior Member
@Michael: I have read that article before, and re-read it again, but I have read-only access to the database. I cannot create database objects.

@flyboy:same problem as above--I cannot create db objects.

Any ideas?
Re: Passing variable values within a CASE statement [message #441687 is a reply to message #441686] Tue, 02 February 2010 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the only way is to remove the CASE last line and add:
OR ( :Division = 'ALL' AND table.division_id in ('02','07','09') )

Regards
Michel
Re: Passing variable values within a CASE statement [message #441690 is a reply to message #441676] Tue, 02 February 2010 11:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if your code is a representative example of what you're trying to do then you can rewrite that with a couple of ORs. If you really need something more dynamic then you need to do one of the following:
a) persuade someone who can to give you the ability to create objects.
b) persuade someone who can to create the objects for you.
c) rewrite the whole query as dynamic sql - but bare in mind this will decrease performance and increase the likelyhood of bug and generally make the code harder to maintain.

Just pester someone until you achieve a or b.
Re: Passing variable values within a CASE statement [message #441691 is a reply to message #441687] Tue, 02 February 2010 11:44 Go to previous messageGo to next message
SQL_Hacker
Messages: 5
Registered: February 2010
Junior Member
Michel,

I'm sorry, I don't understand your response. Do you mean add a second case statement? Or use ELSE? Thank you again for your help.
Re: Passing variable values within a CASE statement [message #441692 is a reply to message #441690] Tue, 02 February 2010 11:47 Go to previous messageGo to next message
SQL_Hacker
Messages: 5
Registered: February 2010
Junior Member
Cookiemonster,

What is "ORs"? I need this query as the basis for a report I'm writing in SQL Server Reporting Services, and the list doesn't need to by dynamic--the three divisions in our company won't change anytime soon--so it can be static. But...I need a way of passing the multi-value string into the case statement so that it will include all 3 divisions, not just one... And...yes, there is the nagging issue of read-only access. Been there, tried that...not going to happen. Smile
Re: Passing variable values within a CASE statement [message #441693 is a reply to message #441676] Tue, 02 February 2010 11:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I mean OR, like an AND but an OR.
Which is basically what Michel is telling you as well:
where table.division_id = (CASE WHEN :Division = 'AA' THEN '02'
			        WHEN :Division = 'BB' THEN '09'
				WHEN :Division = 'CC' THEN '07'
			   END CASE) 
OR ( :Division = 'ALL' AND table.division_id in ('02','07','09') )

Re: Passing variable values within a CASE statement [message #441694 is a reply to message #441693] Tue, 02 February 2010 12:14 Go to previous message
SQL_Hacker
Messages: 5
Registered: February 2010
Junior Member
Woo-hoo!!!!! That works!!!

Thank you very much!!!
Previous Topic: Why DBMS_CHANGE_NOTIFICATION
Next Topic: PL/SQL Table
Goto Forum:
  


Current Time: Sun Sep 25 21:29:23 CDT 2016

Total time taken to generate the page: 0.04963 seconds