CODEWITHSUNDEEP
sqlsql-server
LT268
LT268

Reputation: 135

Subquery returned more than 1 row when using cross apply

I have a requirement to produce a report of customer details as per below code. My additional requirement is to produce a report by having a new record if my email id contain more than 1 value. It should be like this ,

C000279   Cust1      [email protected]
C000279   Cust1      [email protected]

My code is like this,

select distinct c.PKEY [Customer Code],
c.NAME [Customer Name],
mi.MODEL [Machine #],
(select Split.a.value('.','varchar(100)') email
from 
    (select       
        cast('<x>' + replace(m.CONTACT_EMAIL,';','</x><x>')+'</x>' as XML) as x
    from tblMachineContact m
    where mi.PKEY=m.MAC_PKEY and m.CONTACT_CATEGORY_PKEY in ('PC'))t
cross apply x.nodes ('/x') as Split(a)) [Customer Email]
from tblMachine mi
inner join tblCustomers on m.CUST_PKEY=c.PKEY
where mi.STATUS='A'

Since my email id is having more than one value i wanted to split it into another row. But my subquery is returning more than 1 rows due to this and causing error. Any solution to achieve this?

@Gordon, I am using one more where condition. But this email id from outer apply for category=PC is applying to all my category. I only want it to apply to my records with PC category. How can i achieve?

Below is my query,

select distinct c.PKEY [Customer Code],
c.NAME [Customer Name],
mi.MODEL [Machine #],
(select Split.a.value('.','varchar(100)') email
from 
    (select       
        cast('<x>' + replace(m.CONTACT_EMAIL,';','</x><x>')+'</x>' as XML) as x
    from tblMachineContact m
    where mi.PKEY=m.MAC_PKEY and m.CONTACT_CATEGORY_PKEY in ('PC'))t
cross apply x.nodes ('/x') as Split(a)) [Customer Email]
from tblMachine mi
inner join tblCustomers on m.CUST_PKEY=c.PKEY
where mi.STATUS='A' and m.CONTACT_CATEGORY_PKEY in ('PC','TC','ER')

Upvotes: 0

Views: 333

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

I don't follow all the nuances of the code, but this should work if you use outer apply instead of a correlated subquery:

select distinct c.PKEY [Customer Code], c.NAME [Customer Name],
       mi.MODEL [Machine #],
       x.[Customer Email]
from tblMachine mi inner join
     tblCustomers c
     on m.CUST_PKEY = c.PKEY outer apply
     (select Split.a.value('.','varchar(100)') as [Customer Email]
      from (select cast('<x>' + replace(m.CONTACT_EMAIL,';','</x><x>')+'</x>' as XML) as x
            from tblMachineContact m
            where mi.PKEY = m.MAC_PKEY and m.CONTACT_CATEGORY_PKEY in ('PC')
           ) t cross apply
           x.nodes('/x') as Split(a)
     ) x
where mi.STATUS = 'A';

Upvotes: 1

Related Questions