0

I'm passing a JSON string to a stored procedure in order to insert warehouse inventory.

Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (LPN), with the same date. I would like to aggregate those into one line in the table.

The JSON represents individual lines that have been received in, and put away into a location. Here there are 2 identical items:

[{"StockCode": "ABC123", "Qty": "200", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:35:53 UTC"}, {"StockCode": "ABC123", "Qty": "400", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:36:43 UTC"}]

So:

ABC | 200 |  2022-01-21 00:00:00.000 | LPN1234
ABC | 400 |  2022-01-21 00:00:00.000 | LPN1234

Should aggregate to:

ABC | 600 |  2022-01-21 00:00:00.000 | LPN1234

I tried to GROUP BY and SUM on the qty but the resulting lines in the table are still 2 separate lines. I realized the PutDate were not the same due to the timestamp, so I thought for sure casting it to DATE would solve it, but it did not.

SQL script:

ALTER Procedure spc_PutAway
(@json NVARCHAR(MAX) = '')
AS
BEGIN


INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN
    FROM OPENJSON(@json)
    WITH (
        Bin VARCHAR(20) '$.Bin',
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty',
        PutDate VARCHAR(20) '$.PutDate',
        LPN VARCHAR(50) '$.LPN'
        )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN

END

Results in table:

Bin StockCode   Qty PutDate VerDate LPN TransID VerCol
E4B4_L  ABC123  200.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    1   0x000000000000275D
E4B4_L  ABC123  400.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    2   0x000000000000275E

1 Answer 1

5

It's a coincidence that your query even works.

Normally, you cannot use an alias in the GROUP BY clause of a query--you have to use the actual column name. For example, If you try to run this simple query you'll get the error Invalid column name 'ObjectName' when you try to execute the query (note that it will parse successfully, but only error on execution):

SELECT ObjectName = OBJECT_NAME(object_id),
        NumberColumns = COUNT(*)
FROM sys.columns
GROUP BY ObjectName;

To fix this, you'd either use the non-aliased column name, or add in a CTE or subquery to force the layer of abstraction & make it so that the query engine understands the alias.

In your query, you coincidentally use the same name for the shredded column from the OPENJSON...WITH and the computed alias in your select. This obscures the real issue. If you change the name you're using to come out of the JSON shredding & the reference in your SELECT, but continue to use the Alias in your GROUP BY, you'll get an Invalid column error message:

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
    FROM OPENJSON(@json)
    WITH (
        Bin VARCHAR(20) '$.Bin',
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty',
        PutDateText VARCHAR(20) '$.PutDate', --changed this (and reference above)
        LPN VARCHAR(50) '$.LPN'
        )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;

As an aside, now that we've changed some of the labels, we can see that the functional equivalent of your original query is actually this:

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
    FROM OPENJSON(@json)
    WITH (
        Bin VARCHAR(20) '$.Bin',
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty',
        PutDateText VARCHAR(20) '$.PutDate',
        LPN VARCHAR(50) '$.LPN'
        )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDateText, LPN;

The "quick" way to make this work is not using the alias, and just using the direct column name (in this case, a formula using the CAST() function:

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
    FROM OPENJSON(@json)
    WITH (
        Bin VARCHAR(20) '$.Bin',
        StockCode VARCHAR(30) '$.StockCode',
        Qty DECIMAL(18,6) '$.Qty',
        PutDateText VARCHAR(20) '$.PutDate',
        LPN VARCHAR(50) '$.LPN'
        )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, CAST(PutDateText AS DATE), LPN

Personally, I'd use a CTE to shred the JSON into a tabular format, then construct my query using the CTE to do the aggregates. This feels a bit "cleaner" to me and allows me to use the alias names wherever I want. This has an advantage of not having to repeat function calls & keep them in perfect unison between SELECT & GROUP BY:

WITH JsonTable AS (
    SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN
        FROM OPENJSON(@json)
        WITH (
            Bin VARCHAR(20) '$.Bin',
            StockCode VARCHAR(30) '$.StockCode',
            Qty DECIMAL(18,6) '$.Qty',
            PutDate VARCHAR(20) '$.PutDate',
            LPN VARCHAR(50) '$.LPN'
            )
)
INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, 
        StockCode, 
        SUM(Qty) as Qty, 
        PutDate, 
        GETDATE() AS VerDate, 
        LPN
FROM JsonTable
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;

But you could also use an inline subquery in much the same way.

INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN
FROM (
        SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN
            FROM OPENJSON(@json)
            WITH (
                Bin VARCHAR(20) '$.Bin',
                StockCode VARCHAR(30) '$.StockCode',
                Qty DECIMAL(18,6) '$.Qty',
                PutDate VARCHAR(20) '$.PutDate',
                LPN VARCHAR(50) '$.LPN'
                )
    ) AS x
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;
4
  • thanks so much, this solves the issue. I need to get a better feel for abstraction layers. I actually had already started working the CTE method but I'm finding I can't make a second DML call referencing that CTE. A few search suggest that you can only use a CTE once, which doesn't make sense to me. Isn't the point of it that it to be 'common'ly used?
    – hap76
    Jan 25, 2022 at 19:41
  • I tried to follow the INSERT with an UPDATE on another table: UPDATE UserReceivedQty SET UserReceivedQty.ReceivedQty = UserReceivedQty.ReceivedQty - ( SELECT Sum(Qty) FROM json_results WHERE UserReceivedQty.StockCode = json_results.StockCode GROUP BY StockCode, Bin, PutDate, LPN )
    – hap76
    Jan 25, 2022 at 19:54
  • The CTE is a clause as part of a statement. If you need to have both an INSERT statement and an UPDATE statement, both statements would need CTEs. Depending on your scenario, you might shred the JSON into a #temp table and reference that. Depending on your actual workload, the "best" answer will vary with the trade offs of shredding JSON multiple times vs persisting to a #temp table & using the pre-shredded table
    – AMtwo
    Jan 25, 2022 at 22:58
  • Ah I see- it's for use/reuse within a statement. Not to be used by multiple statements. Thanks for taking the time to clarify this for me.
    – hap76
    Jan 26, 2022 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.