Home » SQL & PL/SQL » SQL & PL/SQL » please help me in SQL statement
please help me in SQL statement [message #387827] Fri, 20 February 2009 14:52 Go to next message
ldhullipalla
Messages: 4
Registered: January 2009
Location: Reston
Junior Member
Hi,

select col1, col2,
col3,col4,col5
FROM Table_name
WHERE nvl(col1,'%') LIKE TO_CHAR(:col1)|| '%'
AND nvl(col2,'%') LIKE TO_CHAR(:col2)|| '%'
AND nvl(col3,'%') LIKE TO_CHAR(:col3)|| '%'
AND nvl(col4,'%') LIKE TO_CHAR(:col4)|| '%'
AND col5 is not null
order by 1,2,3

i will pass values for bind variables :col1,:col2,:col3,:col4 from forms.then the qwery will look like this

select col1, col2,
col3,col4,col5
FROM Table_name
WHERE nvl(col1,'%') LIKE TO_CHAR(01)|| '%'
AND nvl(col2,'%') LIKE TO_CHAR('M')|| '%'
AND nvl(col3,'%') LIKE TO_CHAR(2009)|| '%'
AND nvl(col4,'%') LIKE TO_CHAR(null)|| '%'--if i didn't pass any value
AND col5 is not null
order by 1,2,3

here i need to pass more than one value for this select statement,instead of "like" i have to use "in" operator with out changing the query meaning

please help me,

Thanks
Lakshman

Re: please help me in SQL statement [message #387828 is a reply to message #387827] Fri, 20 February 2009 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't in one statement.

Regards
Michel
Re: please help me in SQL statement [message #388663 is a reply to message #387827] Wed, 25 February 2009 21:26 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
there are ways, but none of them particularly pretty, and all have their limits. Consider this code snippet and how you might exploit it in your problem:

SQL> set feedback 1
SQL> l
  1  select substr(      ','||'abc,def,ghi'||','
  2               ,instr(','||'abc,def,ghi'||',',',',1,rownum)+1
  3               ,instr(','||'abc,def,ghi'||',',',',1,rownum+1)-instr(','||'abc,def,ghi'||',',',',1,rownum)-1
  4               ) element
  5  from dual
  6* connect by level <= (length('abc,def,ghi')-length(replace('abc,def,ghi',',')))+1
SQL> /

ELEMENT
-------------
abc
def
ghi

3 rows selected
.

good luck, Kevin
Previous Topic: Running a batch file from oracle
Next Topic: TEXT_IO
Goto Forum:
  


Current Time: Tue Dec 06 14:14:28 CST 2016

Total time taken to generate the page: 0.09045 seconds