Home » SQL & PL/SQL » SQL & PL/SQL » IN param substiution problem....
icon10.gif  IN param substiution problem.... [message #218173] Wed, 07 February 2007 02:06 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

Working code,

 select * from g... where c... in ('W...','O...');


result : returns 2 rows - 1 for 'W...' & 1 for 'O...'
____________________________________________________________

instead if i give below its not functioning...

select * from g... where c... in (a);

here a is the input parameter which has same value like previous query. i.e. a = 'W...','O...'.

result : no values return..

please help out....

-fortunethiyagu

[Updated on: Fri, 01 June 2007 17:37] by Moderator

Report message to a moderator

Re: IN param substiution problem.... [message #218177 is a reply to message #218173] Wed, 07 February 2007 02:13 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
correct your code like ('a') instead of (a);
select * from g... where c... in [B]('a')[/B];



regards
Taj

[Updated on: Fri, 01 June 2007 17:39] by Moderator

Report message to a moderator

Re: IN param substiution problem.... [message #218180 is a reply to message #218177] Wed, 07 February 2007 02:21 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Thanx for ur reply..

If i give ('a'), then it will turn as c... = 'a'.

i just want to subtitute the value of variable a, not 'a'.

-fortunethiyagu

[Updated on: Fri, 01 June 2007 17:41] by Moderator

Report message to a moderator

Re: IN param substiution problem.... [message #218187 is a reply to message #218173] Wed, 07 February 2007 02:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
what about this solution?
Not sure, what type is your parameter a (varchar2 or collection). So adjust it yourself.
Re: IN param substiution problem.... [message #218189 is a reply to message #218173] Wed, 07 February 2007 03:18 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Actualy i am instructed to use VARCHAR2.

Any soultion for this apart from split functions?

[Updated on: Wed, 07 February 2007 04:02]

Report message to a moderator

Re: IN param substiution problem.... [message #218421 is a reply to message #218173] Thu, 08 February 2007 03:03 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
This works for comma separated not quoted values without commas:
SQL> with t as (select 'abc,def,ghijkl,mno' v from dual)
  2  select substr(t.v,instr(','||t.v,',',1,level),instr(t.v||',',',',1,level)-instr(','||t.v,',',1,level)) "Value"
  3  from t
  4  connect by level <= length(t.v)-length(replace(t.v,','))+1;

Value
------------------
abc
def
ghijkl
mno
Re: IN param substiution problem.... [message #218429 is a reply to message #218189] Thu, 08 February 2007 03:21 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How about William's Solution?
By
Vamsi
Previous Topic: Difference between store procedure and package procedure
Next Topic: select username
Goto Forum:
  


Current Time: Mon Dec 05 19:26:42 CST 2016

Total time taken to generate the page: 0.11329 seconds