Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query.
SQL Query. [message #375894] Mon, 15 December 2008 05:06 Go to next message
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 #375896 is a reply to message #375894] Mon, 15 December 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search for "varying inlist".
Asked almost every week.

Regards
Michel
Re: SQL Query. [message #375897 is a reply to message #375894] Mon, 15 December 2008 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't believe it's possible to use a comma seperated list directly like that. You can convert it to table type and use that however.

Have a look at this thread on asktom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425
Re: SQL Query. [message #375952 is a reply to message #375894] Mon, 15 December 2008 07:38 Go to previous messageGo to next message
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 #375964 is a reply to message #375952] Mon, 15 December 2008 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Very good to kill your instance.

@OP, if you are working in PL/SQL you don't need to query DUAL just check:
instr(','||func()||',', ','||this_char||',') = 0

Regards
Michel
Re: SQL Query. [message #376069 is a reply to message #375964] Mon, 15 December 2008 22:50 Go to previous messageGo to next message
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 #376087 is a reply to message #376069] Tue, 16 December 2008 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes.

Regards
Michel
icon7.gif  Re: SQL Query. [message #376091 is a reply to message #376087] Tue, 16 December 2008 00:37 Go to previous messageGo to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
hi michel, thank u very much....for your quick reply... Smile

select 'Y' from dual where this_char > 126 and
not exists (select 'Y' from dual where instr(','||v_string ||',', ',' || this_char ||',')> 0)

Will this query check whether the variable this_char is not in the v_string.

Re: SQL Query. [message #376095 is a reply to message #376091] Tue, 16 December 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select 'Y' from dual 
where to_number(this_char) > 126
  and instr(','||v_string ||',', ','||this_char ||',') = 0

Use code tags!

Regards
Michel
Re: SQL Query. [message #417757 is a reply to message #376095] Tue, 11 August 2009 07:23 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Order by Issue in oracle ?
Next Topic: Perfomance tunning
Goto Forum:
  


Current Time: Wed Feb 12 08:14:16 CST 2025