Why is CROSS APPLY resulting in one less row?

Question

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?

Your new cross apply must not return a value for one row. Try outer apply and see which row it is — Dale K, Jan 26 '23 at 07:51
I mean by outer apply, I still have the problem of a missing row — Nauman Khan, Jan 26 '23 at 08:02
What do mean by 'raw values'? I think they are there in the first two rows — Nauman Khan, Jan 26 '23 at 08:03
I can't think of any way using an outer apply would remove a row. — Dale K, Jan 26 '23 at 08:10
Presumably `Pay_Property` has fewer elements when split than the other columns do. If they return 2 elements and that only returns one you will lose a row due to `RR.[key] = PP.[key]`. Why are you storing the data like this anyway? RepayRef, RepayAmount and pay property should be stored as rows in their own table not stored as delimited strings and split apart and reassembled into rows by ordinal position — Martin Smith, Jan 26 '23 at 08:16
@MartinSmith Yes you are right about the 3 columns. They should not be here. They are here just for a reference to show you. Only the final split values will go in this table. — Nauman Khan, Jan 26 '23 at 10:24

Charlieface · Accepted Answer · 2023-01-26T10:23:46.060

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

Returns incorrect syntax near PP. `FROM OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]') PP` — Nauman Khan, Jan 26 '23 at 10:14
My bad. I should have noticed that. Works like a charm now. Thanks mate! :) — Nauman Khan, Jan 26 '23 at 10:37

Why is CROSS APPLY resulting in one less row?

1 Answers1