OraFAQ Forum: SQL & PL/SQL » In clause not working

Home » SQL & PL/SQL » SQL & PL/SQL » In clause not working (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

In clause not working [message #596137]

Wed, 18 September 2013 04:40

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi All,

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');

IF I use this query directly in DB it's working fine.
But when this query is calling in .net using parameter as below
it's not giving any records for more than one value.
IN (:p_mname)

If I pass one name 'KIRAN-KUMAR' from .net It's working.

If I pass multiple names from .net query not returning any records.

Please help me how to resolve this.

Thanks.

Report message to a moderator

Re: In clause not working [message #596138 is a reply to message #596137]

Wed, 18 September 2013 04:43

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Search keywords here are "varying elements in IN list".

Report message to a moderator

Re: In clause not working [message #596142 is a reply to message #596137]

Wed, 18 September 2013 05:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Here's an example:

SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select substr(:mylist,
  2                instr(','||:mylist||',', ',', 1, rn),
  3                instr(','||:mylist||',', ',', 1, rn+1)
  4                - instr(','||:mylist||',', ',', 1, rn) - 1) value
  5  from ( select level rn from dual 
  6         connect by level 
  7                      <= length(:mylist)-length(replace(:mylist,',',''))+1
  8       )
  9  /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45

SQL> with list as (
  2    select substr(:mylist,
  3                  instr(','||:mylist||',', ',', 1, rn),
  4                  instr(','||:mylist||',', ',', 1, rn+1)
  5                  - instr(','||:mylist||',', ',', 1, rn) - 1) value
  6    from (select level rn from dual 
  7          connect by level 
  8                       <= length(:mylist)-length(replace(:mylist,',',''))+1)
  9    )
 10  select id, valeur
 11  from t
 12  where id IN ( select value from list )
 13  order by id
 14  /
        ID USERNAME
---------- ------------------------------
         5 SYSTEM
        11 OUTLN
        22 MICHEL
        23 OPS$MCADOT101205
        31 SCOTT

5 rows selected.

Report message to a moderator

Re: In clause not working [message #596151 is a reply to message #596137]

Wed, 18 September 2013 05:41

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

ajaykumarkona wrote on Wed, 18 September 2013 11:40

But when this query is calling in .net using parameter as below
it's not giving any records for more than one value.
IN (:p_mname)

Hi,

Another way is to pass a collection including search list values.

Therefore

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN (:p_mname)

Becomes something like

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN (SELECT column_name FROM TABLE(<your collection here>))

Regards,
Dariyoosh

Report message to a moderator

Previous Topic:	How to remove when others then
Next Topic:	Oracle Join on Tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:16:16 CDT 2024