Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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/Received on Thu Aug 11 2005 - 03:09:40 CDT
![]() |
![]() |