-2

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
KVN
  • 863
  • 1
  • 17
  • 35
  • Are you sure you want an outer apply and not a join? You can see the issue right? You're getting multiple `FCBID` values which when using an outer apply multiples your rows. In fact based on your sample data you just want to avoid returning any values starting `DEF`... Although the DEF rows are definitely associated with invoice 718, so what is the premise for excluding them? You need to be clear on the data you want before we can assist. – Dale K Mar 09 '21 at 22:03
  • I tried a few different approaches with it. But still cannot figure out what should I do (No, sorry I do not see where the issue is exactly). The table has more data and the SPROC is going thru quite a few other tables to get the data. In the current sample, it needs only ABC1/2/3, but in other it could be different. – KVN Mar 09 '21 at 22:08
  • Forget the approached, you need to explain how the data is related, i.e. why the DEF rows should be excluded because as your question stands they should be returned, you haven't explained why not. And if there is a relationship between the FCBID table and the invoice table you should be using a join rather than an apply. If you build it up slowly... one table at a time, you'll see where it goes wrong. But banging a great big cross apply on is too much to debug in one go. – Dale K Mar 09 '21 at 22:09
  • I have updated the Invoice table. I suspected that without giving more details on the original SPROC and tables involved - it might be hard to provide help on this. (but decided to give it a shot anyway). – KVN Mar 09 '21 at 22:20
  • On the contrary you should be able to create a [MRE] and in doing so you're probably work out the issue and solve it yourself :) – Dale K Mar 09 '21 at 22:21
  • Thanks Dale. Let me try that :) – KVN Mar 09 '21 at 22:22
  • Updating your invoice table doesn't help - you still haven't explained why the DEF codes shouldn't be being displayed. You need to clarify the relationship such that we understand how you are trying to pull the data. – Dale K Mar 09 '21 at 22:22

1 Answers1

0

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];
KVN
  • 863
  • 1
  • 17
  • 35