SQL Query Error

[hype1999]

When I run this SQL query, I get the following error:

injury_or_illness, cust from test where cust
LIKE (
select code from lookup where lookuparea='DEPARTMENTS' and data_id in (
select data_id from lookup_email where receipient = 'Admin'
and notification_type = 'Representative'))||'%';


ORACLE-01427: single-row subquery returns more than one row.

Is there a SQL equivalent that will achieve the above using the LIKE operator?

[rmiao]

Don't cross post.

[hype1999]

Sorry, I am new to this. I just need help that is all. I orginally posted in the wrong place that is why I posted here.

[shamshe]

I don't think you need LIKE here, try this one:
select injury_or_illness, cust from test,lookup
where lookuparea='DEPARTMENTS'
and data_id in (select data_id from lookup_email where receipient = 'Admin'
and notification_type = 'Representative')
and INSTR(cust,code) = 1;

It should return result set with all custs, containing code.

[hype1999]

Thanks for your help. It worked!