RE : IN MISTERY

Sergio,

    IN checks either a hard-coded list or EACH ROW of a subquery - It cannot work with a subquery returning something which looks like a hard-coded list.

   I have an awful feeling of not being very clear but, assuming that none of your 'lista_tipo_publi' is a substring of another one, this should work :

     select ltp.lista_tipo_publi from lista_tipo_publi ltp
     where exists (select null
                   from lista_pautas lp
                   where instr(lp.tipos, ltp.lista_tipo_publi) != 0
                      and lp.id_pauta = '6')

-- 
HTH,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------



> 

>  Hi all

> 

>  I have the next query with the in clause, and it doesn´t works, any

> idea?.

> 

>  I f i execute the query,

>     select'''||replace(tipos, ', ' , ''', ''')||'''' from lista_pautas

> where id_pauta='6'

> i get the result:

> 

>    'palabras', 'negritas', 'sin cargo breves'

> 

> With the query,

>     select lista_tipo_publi from lista_tipo_publi where lista_tipo_publi

> in ('palabras', 'negritas', 'sin cargo breves')

> i get the result:

> 

>    4

>    9

>    50

> 

> But if I try with the quey:

> 

>     select lista_tipo_publi from lista_tipo_publi where lista_tipo_publi

> in

> (select'''||replace(tipos, ', ' , ''', ''')||'''' from lista_pautas

> where id_pauta='6')

> 

>   I get 0 rows as result. Any idea about that. Is the IN clause working

> wrong.

> 

>   Is there another way to do the same query without the IN clause.

> 

> Thanks for your responses,

> 

> Bye.