Re: SQL: partial match with set of vsalues from a subquery

On 2005-08-11, baka <mesundara_at_hotmail.com> wrote:
> Thank you very much AOLO san and BARBARA for the reply.
> I beg your pardon for not explaining my problem.
>
> please see the below examples, i want to use LIKE caluse instead of IN
> clause but it is not working.
>
> 1. The following SQL works for me (AS its an exact match)
>
> select i.accountcode,i.patternname from idsklogdata i where
> i.patternname IN
> (select prjname from prjmaster pj, prjmember pjm where
> PJMEMBERCODE='PM001' AND PRJROLECODE='PM' and
> pjm.prjcode=pj.prjctcode)
>
>
> 2. I chnaged the IN Verb to LIKE verb
>
> select pdata.accountcode,pdata.patternname from prjdata pdata where
> pdata.patternname LIKE '%' ||
> (select prjname from prjmaster pj, prjmember pjm where
> PJMEMBERCODE='PM001' AND PRJROLECODE='PM' and
> pjm.prjcode=pj.prjctcode) || '%'
>
> As the Subquery retruns more than one row so it gives an error:
>
> -japnese message
> ORA-01427:
> ??????????2???????????
>
> --Translated message
> ORA-01227: 2 or more rows are returned from the subquery
>
> Is it possible to change the second SQL
>
> Thank you for reading the mail
>
> PS:
> (At present, To solve the problem i have changed the design . so its
> not urgent but would like to know the workaround)
>

something like this should do:

select pdata.accountcode,pdata.patternname from   prjdata pdata ,
  prjmaster pj,
  prjmember pjm
where
  pdata.patternname LIKE '%' || prjname || '%' and   pjm.prjcode = prjctcode;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/