Asked By
Anand
110 points
N/A
Posted on - 04/28/2011
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.
Answered By
Shaun
0 points
N/A
#89213
Oracle Equivalent Query on a Given Situation
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;
Answered By
Anand
110 points
N/A
#89214
Oracle Equivalent Query on a Given Situation
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?
Answered By
Shaun
0 points
N/A
#89215
Oracle Equivalent Query on a Given Situation
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;
Answered By
Anand
110 points
N/A
#89217
Oracle Equivalent Query on a Given Situation
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?
Answered By
Shaun
0 points
N/A
#89218
Oracle Equivalent Query on a Given Situation
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.
Â
