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

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) Received on Thu Aug 11 2005 - 00:29:49 CDT