SQL & PL/SQL

Hi,


I am trying to use LIKE operator in the below subquery. The subquery will return more than one row.

I am getting error in the LIKE operator condition.

Can anyone please help me out how to use LIKE Condition for multiple rows sub query.

SELECT
msi.segment1,
msi.description,
msi.unit_weight,
msi.unit_volume, mcb.segment1 category_code
from
mtl_system_items_b msi,
mtl_item_categories mic,
mtl_category_sets_tl mcs,
mtl_categories_b mcb
where
msi.segment1 like
'%' ||SELECT hca.orig_system_reference
from
hz_parties hz,hz_cust_Accounts hca
where
hz.party_id=hca.party_id
and
hz.party_name = 'Asda direct'|| '%'

Regards
Prabu