Oracle Equivalent Query on a Given Situation

Asked By 110 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by Shaun
Best Answer
Best Answer
Answered By 0 points N/A #89213

Oracle Equivalent Query on a Given Situation

qa-featured

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 110 points N/A #89214

Oracle Equivalent Query on a Given Situation

qa-featured

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 0 points N/A #89215

Oracle Equivalent Query on a Given Situation

qa-featured

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 110 points N/A #89217

Oracle Equivalent Query on a Given Situation

qa-featured

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 0 points N/A #89218

Oracle Equivalent Query on a Given Situation

qa-featured

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.

 

Login/Register to Answer

Related Questions