Home » SQL & PL/SQL » SQL & PL/SQL » complex query (Oracle9i ,Windows)
complex query [message #277948] Thu, 01 November 2007 05:22 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi
In one table contains two columns a and b

I am searching the value based on a or b

if a is there i have to search based on a
select * from table a= ?

if b is there i have to search based on b
select * from table b=?

if a and b is there
i have to search based on a and b
select * form table a = ? and b = ?

i want these three functionality in a single query...

Note : use disabling the bind variable concept

Re: complex query [message #277956 is a reply to message #277948] Thu, 01 November 2007 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean you don't know if your table contains columns a and b, or do you mean you don't if columns a and/or b contains a value?

In the latter, have a look at "nvl" function or "is null" condition.

Regards
Michel
Re: complex query [message #277957 is a reply to message #277948] Thu, 01 November 2007 05:32 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


can you make it more simple by giving a test case or so.it's difficult to understand.



regards,
Re: complex query [message #277959 is a reply to message #277948] Thu, 01 November 2007 05:37 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

table has two columns a and b

Table structure:

condition 1:
a b

12 null
12 null
12 null
33 null
44 null

now i want to select * from table where a=?


condition 2:

a b
null 12
null 12
null 12
null 13

now i want to select * from table where b=?;

condition 3:

a b
12 23
12 33
23 33
98 88

now i want to select * from table where a=? and b=?

i wnat to three functionality in simple query

Note: do not use bind variables:






Re: complex query [message #277972 is a reply to message #277959] Thu, 01 November 2007 06:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
SELECT *
  FROM tablename
 WHERE nvl(a,:a1) = :a1
   AND nvl(b,:b1) = :b1;
Replace a1 and b1 with your values.

By
Vamsi
Re: complex query [message #277973 is a reply to message #277972] Thu, 01 November 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please don't give solution to newbies just give clue then he cann learn much more finding himself the solution.

Regards
Michel
Re: complex query [message #277976 is a reply to message #277973] Thu, 01 November 2007 06:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I try to do that, but some times I fail. Confused

By
Vamsi
Re: complex query [message #277998 is a reply to message #277959] Thu, 01 November 2007 07:06 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Note: do not use bind variables:

Why not?
Previous Topic: LOB data type variables...
Next Topic: Need help with an SP
Goto Forum:
  


Current Time: Thu Dec 08 14:34:46 CST 2016

Total time taken to generate the page: 0.08177 seconds