Why is CROSS APPLY resulting in one less row?-CodePudding

I split some values (REPAY_AMOUNT and REPAY_REF) which are delimited special character and cross applied them as follows:

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) 'Split Date',
        RA.Value 'Split Amt'
        
FROM    AA_BILL_DETAILS_Property

        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA

WHERE   RR.[key] = RA.[key]
ORDER BY    [Split Date] DESC

Results are:

ARRANGEMENT_ID	REPAY_AMOUNT	REPAY_REF	Split Date	Split Amt
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-11-17	3678.41
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-10-20	2050.25
AA21084T517V	1931.41931.4	AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228	2022-02-28	1931.4
AA21084T517V	1931.41931.4	AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228	2022-02-28	1931.4
AA21084T517V	NULL	NULL	1900-01-01
AA21084T517V	NULL	NULL	1900-01-01

Problem:

Now I want to split and cross apply another column (PAY_PROPERTY) like:

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) 'Split Date',
        RA.Value 'Split Amt',
        PAY_PROPERTY,
        PP.Value 'PP'

FROM    AA_BILL_DETAILS_Property

        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP

WHERE   RR.[key] = RA.[key] AND RR.[key] = PP.[key]
ORDER BY    [Split Date] DESC

But doing so, I lose one row (i-e, one of the rows having Split Amt = 1931.4) resulting in:

ARRANGEMENT_ID	REPAY_AMOUNT	REPAY_REF	Split Date	Split Amt	Pay_Property	PP
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-11-17	3678.41	ACCOUNTPRINCIPALINT	ACCOUNT
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-10-20	2050.25	ACCOUNTPRINCIPALINT	PRINCIPALINT
AA21084T517V	1931.41931.4	AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228	2022-02-28	1931.4	NULL
AA21084T517V	NULL	NULL	1900-01-01		NULL
AA21084T517V	NULL	NULL	1900-01-01		NULL

Can someone help please?

CodePudding user response：

Your problem is that you have an unequal number of split values in each column, so the join condition is failing. You cannot use an ON clause with APPLY, so instead you can use OUTER APPLY, which works similarly to a LEFT JOIN, and move the WHERE condition into the APPLY.

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) [Split Date],
        RA.Value [Split Amt],
        PAY_PROPERTY,
        PP.Value PP

FROM    AA_BILL_DETAILS_Property
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
OUTER APPLY (
    SELECT *
    FROM OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
    WHERE RR.[key] = PP.[key]
) PP
WHERE   RR.[key] = RA.[key]
ORDER BY
    [Split Date] DESC

If the other columns also have a variable number of splits then you need a full join:

SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        PAY_PROPERTY,
        v.*
FROM    AA_BILL_DETAILS_Property
OUTER APPLY (
    SELECT
        CAST(RIGHT(RR.Value,8) AS DATE) [Split Date],
        RA.Value [Split Amt],
        PP.Value PP
    FROM OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
        ON RR.[key] = RA.[key]
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
        ON ISNULL(RA.[key], RR.[key]) = PP.[key]
) v
ORDER BY
    [Split Date] DESC

Note the ISNULL in the second join condition.

db<>fiddle