Search code examples
sqlsql-serversql-server-2019

Cross apply on subquery


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.


Solution

  • 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