I am using SQL Server 2019
I have a query that uses a few subqueries with union all and cross apply using string_split on both the sub query and the main query.
The cross apply in the subqueries works fine but the cross apply on the outer query is not working.
select
DISTINCT
c.partynumber
,ELECTRONICADDRESSID
,COUNTRYREGIONCODE
,DESCRIPTION
,ISINSTANTMESSAGE
,ISMOBILEPHONE
,CASE
when isnumeric(PURPOSE) = 1
then
case
when purpose = 1
then 'Yes'
else 'No'
end
else
'No'
end as ISPRIMARY
,LOCATIONID
,LOCATOR
,LOCATOREXTENSION
,case
when isnumeric(PURPOSE) = 1
then
case
when purpose = 1
then 'Business;Invoice;Remit to;Statement;Purchase Order'
else
'Business'
end
else
purpose
end
as purpose
,TYPE
from
(
--vendors
select
vpm.PARTYNUMBER PARTYNUMBER
,'auto generated' ELECTRONICADDRESSID
,'' COUNTRYREGIONCODE
,'Email address' DESCRIPTION
,'' ISINSTANTMESSAGE
,'' ISMOBILEPHONE
,'' ISPRIMARY
,'' ISPRIVATE
,'auto generated' LOCATIONID
,(SELECT nicholas.dbo.name_and_address_master.na_name
FROM nicholas.dbo.name_and_address_master
WHERE nicholas.dbo.name_and_address_master.accountcode = cm.cre_accountcode
AND nicholas.dbo.name_and_address_master.na_type = 'E') LOCATOR
,'' LOCATOREXTENSION
,cast(row_number() over (partition by vpm.partynumber order by vpm.partynumber) as varchar(max)) PURPOSE
, 'Email Address' as TYPE
from
nicholas.dbo.cre_master cm
left join
mapping.dbo.vendor_party_mapping vpm on vpm.xi_code = cm.cre_accountcode
where
(SELECT nicholas.dbo.name_and_address_master.na_name
FROM nicholas.dbo.name_and_address_master
WHERE nicholas.dbo.name_and_address_master.accountcode = cm.cre_accountcode
AND nicholas.dbo.name_and_address_master.na_type = 'E') != ''
and
vpm.PARTYNUMBER is not null
UNION ALL
--customers
select
(select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = accountcode) PARTYNUMBER
,'auto generated' ELECTRONICADDRESSID
,'' COUNTRYREGIONCODE
,'Email address' DESCRIPTION
,'' ISINSTANTMESSAGE
,'' ISMOBILEPHONE
,'' ISPRIMARY
,'' ISPRIVATE
,'auto generated' LOCATIONID
,(SELECT top 1 CONCAT(naam.na_name, naam.na_company)
FROM nicholas.dbo.name_and_address_master naam
WHERE naam.accountcode = dm.accountcode
AND naam.na_type = 'E') LOCATOR
,'' LOCATOREXTENSION
,cast(row_number() over (partition by dm.accountcode order by dm.accountcode) as varchar(max)) PURPOSE
, 'Email Address' as TYPE
from
nicholas.dbo.deb_master dm
left join
mapping.dbo.customer_id_master cim on cim.xi_code = dm.accountcode
where
dm.dr_cust_type != '..'
and
exists (select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = accountcode)
and
(SELECT top 1 CONCAT(naam.na_name, naam.na_company)
FROM nicholas.dbo.name_and_address_master naam
WHERE naam.accountcode = dm.accountcode
AND naam.na_type = 'E') != ''
UNION ALL
--true forms
select
(select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = b.XiAccountCode) PARTYNUMBER
,'auto generated' ELECTRONICADDRESSID
,'' COUNTRYREGIONCODE
,b.DocumentType DESCRIPTION
,'' ISINSTANTMESSAGE
, '' ISMOBILEPHONE
,'' ISPRIMARY
,'' ISPRIVATE
, 'auto generated' LOCATIONID
,b.value LOCATOR
,'' LOCATOREXTENSION
,case
when b.DocumentType = 'Invoice'
then 'Business;Invoice'
when b.DocumentType = 'Customer Statement'
then 'Business;Statement'
when b.DocumentType = ' Remittance'
then 'Business;Remit To'
else 'Business'
end as PURPOSE
, 'Email Address' as TYPE
from
(SELECT *
FROM (SELECT
tdm.tdm_type AS CustomerOrVendor,
tdm.contact_type,
tdm.tdm_account AS XiAccountCode,
CASE
WHEN tdm.tdm_document = 'CS'
THEN 'Customer Statement'
WHEN tdm.tdm_document = 'PO'
THEN 'Purchase Order'
WHEN tdm.tdm_document = 'EFT'
THEN 'Remittance'
WHEN tdm.tdm_document = 'CR'
THEN 'Customer Rebate'
WHEN tdm.tdm_document = 'CN'
THEN 'Credit Note'
WHEN tdm.tdm_document = 'PL'
THEN 'POS Layby'
WHEN tdm.tdm_document = 'INV'
THEN 'Invoice'
WHEN tdm.tdm_document = 'POQ'
THEN 'Purchase Quotations'
ELSE 'ERROR'
END AS DocumentType,
CASE
WHEN tdm.contact_type = 'A'
THEN (ISNULL((SELECT CONCAT(nicholas.dbo.name_and_address_master.na_name, nicholas.dbo.name_and_address_master.na_company)
FROM nicholas.dbo.name_and_address_master
WHERE nicholas.dbo.name_and_address_master.accountcode = tdm.tdm_account
AND nicholas.dbo.name_and_address_master.na_type = 'E'),''))
WHEN tdm.contact_type = 'M'
THEN tdm.tdm_address
ELSE 'ERROR'
END AS EmailAddress
FROM nicholas.dbo.trueform_document_map tdm) a
CROSS APPLY STRING_SPLIT(a.EmailAddress, ';') as LOCATOR --this cross apply works
WHERE a.EmailAddress != '' and a.EmailAddress like '%@%'
) b
) C
CROSS APPLY STRING_SPLIT(c.LOCATOR, ';') as LOCATOR --this cross apply does NOT work
where c.Locator like '%@%'
and (c.PARTYNUMBER is not null and c.partynumber != '')
--and c.DESCRIPTION != 'Customer Statement'
and c.locator like '%[A-Z0-9][@][A-Z0-9]%[.][A-Z0-9]%'
order by c.PARTYNUMBER
I am expecting individual split rows but am still getting rows with semicolons in them.
Try modifying your query in the following way (I've chopped out the middle chunk, because it's a very long query, and you really only need to modify the last "string_split()", and then include an additional output column in the main "outer" SELECT statement.
select
DISTINCT
c.partynumber
,ELECTRONICADDRESSID
,COUNTRYREGIONCODE
,DESCRIPTION
,ISINSTANTMESSAGE
,ISMOBILEPHONE
,CASE
when isnumeric(PURPOSE) = 1
then
case
when purpose = 1
then 'Yes'
else 'No'
end
else
'No'
end as ISPRIMARY
,LOCATIONID
,LOCATOR
, LOCATOR2.[value]
... (all the "insides" of the query goes here)
CROSS APPLY STRING_SPLIT(c.LOCATOR, ';') as LOCATOR2 --this cross apply does NOT work
where c.Locator like '%@%'
and (c.PARTYNUMBER is not null and c.partynumber != '')
--and c.DESCRIPTION != 'Customer Statement'
and c.locator like '%[A-Z0-9][@][A-Z0-9]%[.][A-Z0-9]%'
order by c.PARTYNUMBER