Home » SQL & PL/SQL » SQL & PL/SQL » comparing single value with many values at one go (oracle 10g)
comparing single value with many values at one go [message #421191] Sat, 05 September 2009 06:15 Go to next message
drop2priya
Messages: 1
Registered: November 2008
Junior Member
I want to compare single integer value with series of values in plsql without getting into a loop.

I tried using IN, but here i cannot pass a variable with many values to be compared as below.
if (i in p_values)

but this one works like
if (i in (1,2,3))

the values to be compared (i.e p_values) are actually got by another logic before this comparison.
so i cannot hardcode these values in comparison.

How can i achieve this comparison without parsing p_values and without going into loop?

Thanks
Priya

Re: comparing single value with many values at one go [message #421194 is a reply to message #421191] Sat, 05 September 2009 07:19 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Create a database type and use table(x_type).
May be something like
create or replace type x_type ....
...
SELECT count(*)
INTO cnt
FROM TABLE(CAST(p_values AS x_type)) b
WHERE b.column_value = i;
IF cnt > 0 THEN
.... 
By
Vamsi

[Updated on: Sat, 05 September 2009 07:41]

Report message to a moderator

Re: comparing single value with many values at one go [message #421198 is a reply to message #421191] Sat, 05 September 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what is the type of p_value and how you get it.
Post an example reprsenting your code.

Also search for "varying in-list" or the like.

Regards
Michel

[Updated on: Sat, 05 September 2009 10:25]

Report message to a moderator

Re: comparing single value with many values at one go [message #421217 is a reply to message #421198] Sun, 06 September 2009 00:28 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There is also always INSTR.

Given these values:
var1='1,2,3'
searchterm='1'
delimiter=','

The following psuedo code should tell you the same thing more or less.
if instr(delimiter||var1||delimiter,delimiter||searchterm||delimiter) > 0 then
   item is in list
else
   item is not in list
end if;

Do not forget to try a set of test cases involving null. The behavior of thie INSTR test may be different from that of a straight forward IN test. Try searching for a searchterm=null and searching where your delimited list has a null as in '1,,3'.

If you find the behavior is different you might also add the following:
and searchterm is not null

But we warry of implicit datatype conversions.

Good luck, Kevin

[Updated on: Sun, 06 September 2009 00:40]

Report message to a moderator

Previous Topic: Procedure Compiles with warnings if I include an IF statement
Next Topic: Trace files location and user commits question (2 merged)
Goto Forum:
  


Current Time: Sun Dec 04 04:31:45 CST 2016

Total time taken to generate the page: 0.05351 seconds