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

Thank you very much PAOLO san and Barbara san for the reply. I beg your pardon for not explaining my problem in a clear manner.

please see the below 2 examples,
i.e 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 pdata.accountcode,pdata.patternname from prjdata pdata where pdata.patternname IN
(select prjname from prjmaster pj, prjmember pjm where
PJMEMBERCODE='PM001' AND PRJROLECODE='PM' and pjm.prjcode=pj.prjctcode)

2. I changed 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-01427: 2 or more rows are returned from the subquery

Is it possible to change the second SQL
Thank you((& sll) 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) Received on Thu Aug 11 2005 - 01:24:31 CDT