Hello everyone,
I have a requirement as below :
If p=s and q=t and r=u then display a value.
I want an equivalent query in Oracle for this.
Thanks in advance.
Hi,
If we translate your query in an If-then-else clause it would look something like this :
if p=s
then
if q=t
then
if r=u
then
display a value
else
null
end if
else
null
end if
else
null
end If
This can be implemented using DECODE statement as shown below:
DECODE(p,s,DECODE(q,t,DECODE(r,u,display a value,NULL),NULL),NULL)
A short and clearer method can be seen in CASE statement :
select case when p=s and q=t and r=u then display a value else NULL end;
Thanks for a detailed explaination. I guess CASE statement was better in terms of understanding and clarity. Can CASE be used in where clause? If yes, then how to implement CASE statement in where clause?
You can try something like this :
select (case when obj_type = 'ABC' then 'PQR' else 'XYZ') test
where (case when obj_type = 'ABC' then 'PQR' else 'XYZ')
not in (select test from tableb)
from t;
Okay. Point taken. Another question here, I want to accomplish the following using DECODE function
if (x>y) then 'Value' else Null
I tried this query :
decode(x>y,true,'Value')
But this syntax is giving me an error. Can you let me know where am I going wrong?
You are missing 'sign'. This function returns 3 values -> +1,-1 and 0. Check this syntax first :
decode(x>y,+1,'Value',Null)
+1 : x>y
-1 : x<y
0 : x=y
I hope this helps.
