Home » SQL & PL/SQL » SQL & PL/SQL » Select case when
icon8.gif  Select case when [message #202961] Mon, 13 November 2006 07:40 Go to next message
cissi
Messages: 3
Registered: November 2006
Junior Member
Hi!
I'm a newbie and I hope someone can help me. I have a query with a lot of parameters. I only want the query to get the data if the condition is true. I have tried with this code :

select
case
when PRO = '10056' then
(select A from TABLE1 where RES = :param1)
else

(select A FROM TABLE1)

end

from TABLE1

But I get the following error:
"ORA-01427 single-row subquery returns more than one row"
So then I tried with this code:

select A FROM TABLE1
WHERE A IN
(select
case
when PRO ='10056' then
(select A from TABLE1 where RES = :param1)
else

(select A FROM TABLE1)

end)
But then I get this error message:"ORA-00923 FROM keyword not found where expected"
Re: Select case when [message #202963 is a reply to message #202961] Mon, 13 November 2006 07:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How about only running the query if the condition is true?
Re: Select case when [message #202965 is a reply to message #202963] Mon, 13 November 2006 07:54 Go to previous messageGo to next message
cissi
Messages: 3
Registered: November 2006
Junior Member
Yes, but how do I write the query?
Re: Select case when [message #202970 is a reply to message #202965] Mon, 13 November 2006 08:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try this.

select * from table1
where RES = decode(PRO,'10056',:param1,RES);



By
Vamsi
Re: Select case when [message #202996 is a reply to message #202970] Mon, 13 November 2006 08:37 Go to previous message
cissi
Messages: 3
Registered: November 2006
Junior Member
Thanks for your quick answer! In my original query I use decode and my big problem is that the query takes to much time. Therefore I only want the query to run when the right condition is true. Here's my code:

SELECT PROC, A, B, AV, RES, PROD, PROJ, R, M, K,O, V
FROM TABLE1
WHERE decode(:ao,NULL,'a',:ao) = DECODE(:ao,NULL,'a',A) and decode(:res,NULL,'a',:res) = DECODE(:res,NULL,'a',RES) and decode(:proc,NULL,'a',:proc) = DECODE(:proc,NULL,'a',PROC) AND decode(:b,NULL,1,:b) = DECODE(:b,NULL,1,B) AND
decode(:av,NULL,1,:av) = DECODE(:av,NULL,1,AV) AND decode(:prod,NULL,'a',:prod) = DECODE(:prod,NULL,'a',PROD) AND decode(:proj,NULL,'a',:proj) = DECODE(:proj,NULL,'a',PROJ) AND
decode(:r,NULL,'a',:r) = DECODE(:r,NULL,'a',R) AND decode(:m,NULL,'a',:m) = DECODE(:m,NULL,'a',M) AND decode(:k,NULL,'a',:k) = DECODE(:k,NULL,'a',K) AND decode(:a,NULL,'a',:a) = DECODE(:a,NULL,'a',O) AND decode(:v,NULL,1,:v) = DECODE(:v,NULL,1,V)

What I would like the query to do is if :ao has a value then SELECT PROC, A, B, AV, RES, PROD, PROJ, R, M, K,O, V
FROM TABLE1 WHERE A = :ao and so on....

Can I do this with decode?
Previous Topic: Merge time duration across data streams based on priority
Next Topic: "ORA-01405" returned with .NET driver on Oracle server 9.2
Goto Forum:
  


Current Time: Sat Dec 03 13:59:19 CST 2016

Total time taken to generate the page: 0.07825 seconds