Oracle Equivalent Query on a Given Situation

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.

• Status: Open
• Question Views: 780
• Answer Count: 5
• Vote Up 11 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Shaun

Go To Solution

Best Answer

Best Answer

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;

About Shaun

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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?

About Anand

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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;

About Shaun

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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?

About Anand

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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.

 

About Shaun

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database