Skip to content
Advertisement

SPROC with Outer-Apply returns too many rows

I have a complicated enough SPROC, which I modified to return a few more columns (from additional tables).

What I wanted is to have this SPROC still to return the same amount of rows and the same data, but to fill in new columns (if data exists) as well, for those rows. My modification does fill new columns, but also returns more rows (based on the values it finds those additional tables).

Here is a sample of the additional table:

Table FCB:

FCBID InvoiceID FCBIDCode CBCode IsSet FCBJrnID
ABC1 718 abcC1 cb1 0 1234
ABC2 718 abcC2 cb1 0 1234
ABC3 718 abcC3 cb1 0 1234
DEF1 718 abcC4 cb1 0 1234
DEF2 718 abcC5 cb1 0 1234
DEF3 718 abcC6 cb1 0 1234
BBB2 334 abcC7 cb2 1 3333
AAA5 225 abcC8 cb3 0 4444

Table Invoices:

InvoiceID ProdID TrackNum
334 P4 T7
718 P1 T1
718 P2 T1
225 P5 T5
225 P6 T6
718 P3 T1
718 P9 T1
718 P3 T2
718 P9 T2
718 P1 T2
718 P2 T2

Table FCBAP:

FCBID ProdID TrackNum
ABC1 P1 T1
ABC2 P2 T1
ABC3 P3 T1
DEF1 P1 T2
DEF2 P2 T2
DEF3 P3 T2
BBB2 P4 T3

The original SPROC (the main part of it) is around the InvoiceID and ProdID, and when I did execute the original SPROC it returned me only 2 rows (the correct behaviour). I.e. Search by InvoiceID = 718

InvoiceID ProdID
718 P1
718 P2
718 P3
718 P9

I have added the following parts to this SPROC:

Added columns into the Main Query:

[FCBI].[FCBID],
[FCBI].[FCBIDCode],
[FCBI].[CBCode],

And I added another Outer Apply part, to take data from additional tables:

OUTER APPLY
    (
        SELECT
            [FCB].[FCBID],
            [FCB].[FCBIDCode],
            [LTC].[FieldText] AS [CBCode]
        FROM [dbo].[FCB] AS [FCB]
        INNER JOIN [dbo].[GenericCodes] AS [GRC] ON [GRC].[Guid] = [FCB].[CBCode]
        CROSS APPLY [dbo].[GetTranslationTable] (@LanguageCode , [GRC].[DescriptionID]) AS [LTC]
        WHERE
            [FCB].[InvoiceID] = [MT].[InvoiceID]
            AND FCB.IsSet = 0
            AND FCB.FCBJrnID = MT.FCBJrnID
    ) AS [FCBI];

What I expect to be returned in this case:

InvoiceID ProdID FCBID FCBIDCode CBCode
718 P1 ABC1 abcC1 cb1
718 P2 ABC2 abcC2 cb1
718 P3 ABC3 abcC3 cb1
718 P9

But what I’m getting instead is:

InvoiceID ProdID FCBID FCBIDCode CBCode
718 P1 ABC1 abcC1 cb1
718 P2 ABC2 abcC2 cb1
718 P3 ABC3 abcC3 cb1
718 P1 DEF1 abcC4 cb1
718 P2 DEF2 abcC5 cb1
718 P3 DEF3 abcC6 cb1
718 P9

Advertisement

Answer

For whatever it worth, and in case it will be clear to anyone looking into this later, the solution was to change the OUTER APPLY block as following:

OUTER APPLY
    (
        SELECT
            [FCB].[FCBID],
            [FCB].[FCBIDCode],
            [LTC].[FieldText] AS [CBCode]
        FROM [dbo].[FCBAP] AS [FCBAP]
        INNER JOIN [dbo].[FCB] AS [FCB] ON [FCB].[FCBID] = [FCBAP].[FCBID]
        INNER JOIN [dbo].[GenericCodes] AS [GRC] ON [GRC].[Guid] = [FCB].[CBCode]
        CROSS APPLY [dbo].[GetTranslationTable] (@LanguageCode , [GRC].[DescriptionID]) AS [LTC]
        WHERE
            [FCBAP].[ProdID] = [MT].[ProdID]
            AND [FCBAP].[TrackNumb] = [MT].[TrackNum]
            [FCB].[InvoiceID] = [MT].[InvoiceID]
            AND FCB.IsSet = 0
            AND FCB.FCBJrnID = MT.FCBJrnID
    ) AS [FCBI];
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement