OraFAQ Forum: SQL & PL/SQL » condition basis where clause

Home » SQL & PL/SQL » SQL & PL/SQL » condition basis where clause (oracle 10 G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

condition basis where clause [message #623347]

Tue, 09 September 2014 02:45

simplesanju
Messages: 36
Registered: July 2008

Member

Hi all,

my requirement to fetch data on few conditional filters like

Table structure

table name:test

column1 column2 column3

1) select * from test where column1=:p1

if record count is zero of first query, then it should show output of below query

2) select * from test where column2=:p2
if record count is zero of 2nd query also, then it should show output of below query

2) select * from test where column3=:p3

I want to merge three query into one.

Thanks
Sanjana

Report message to a moderator

Re: condition basis where clause [message #623350 is a reply to message #623347]

Tue, 09 September 2014 02:58

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

with
  d1 as (select * from test where column1=:p1),
  d2 as (
    select * from d1
    union all
    select * from test 
    where column2=:p2 and not exists (select null from d1)
  )
select * from d2
union all 
select * from test 
where column3=:p3 and not exists (select null from d2)
/

Report message to a moderator

Re: condition basis where clause [message #623352 is a reply to message #623350]

Tue, 09 September 2014 03:29

simplesanju
Messages: 36
Registered: July 2008

Member

Great Thanks Smile

Report message to a moderator

Previous Topic:	ORDER BY gives good performance or INDEX hint gives good performance.
Next Topic:	Time based alerting

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:18:02 CDT 2024