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];