SQL Query. [message #375894] |
Mon, 15 December 2008 05:06  |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
Hi all,
I need a help in the query. I will call a function that will return a set of values separated by comma. For ex: it will return 98,105,230,654...etc like this..Now I want to include these values in a query with not in condition.
i.e
select 'Y' from dual where this_char not in (98,105,230,654,...)
Without the dynamic query can it be possible...please help me on this.....
|
|
|
|
|
Re: SQL Query. [message #375952 is a reply to message #375894] |
Mon, 15 December 2008 07:38   |
subhra_88
Messages: 14 Registered: April 2007 Location: Bangalore
|
Junior Member |
|
|
try like this:
column list new_value list;
select func() list from dual;
select 1 from dual where column not in &list;
The function should return a string with brackets like (1,2,3).
|
|
|
|
Re: SQL Query. [message #376069 is a reply to message #375964] |
Mon, 15 December 2008 22:50   |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
thank u michel for your query...
instead of the function, if i give the procedure, the values will be in out parameter can i use the out parameter instead of func()....
instr(','||v_string||',', ','||this_char||',') = 0
v_string is the list of variables that holds the comma separated values...
v_string = 512,613,847,597.....etc like this....
|
|
|
|
|
|
Re: SQL Query. [message #417757 is a reply to message #376095] |
Tue, 11 August 2009 07:23   |
sivakumar.rj
Messages: 17 Registered: November 2008 Location: Chennai
|
Junior Member |
|
|
Hi Michel,
I have a doubt in this query...why we are appending with the commas in the instr...
instr(','||func()||',', ','||this_char||',')
Can you explain this...
|
|
|
Re: SQL Query. [message #417759 is a reply to message #417757] |
Tue, 11 August 2009 07:31  |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
If you don't how can you check the first and last values.
More precisely how to check '98,...' without getting '...,198,...'?
First and last commas are there to put the first and last values in the same pattern that the other ones.
Regards
Michel
|
|
|