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  |
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 #388663 is a reply to message #387827] |
Wed, 25 February 2009 21:26  |
 |
Kevin Meade
Messages: 2103 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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 15:08:20 CST 2025
|