Início / dba / Perguntas / 306522
Accepted
hap76
Asked: 2022-01-25 10:35:26 +0800 CST

Usando OPENJSON, não é possível obter resultados para GROUP BY

  • 772

Estou passando uma string JSON para um procedimento armazenado para inserir o inventário do armazém.

Às vezes, haverá vários itens de linha com o mesmo produto, indo para a mesma prateleira, no mesmo palete (LPN), com a mesma data. Gostaria de agregá-los em uma linha na tabela.

O JSON representa linhas individuais que foram recebidas e guardadas em um local. Aqui há 2 itens idênticos:

[{"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"}]

Então:

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

Deve agregar para:

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

Eu tentei GROUP BY e SUM no qty, mas as linhas resultantes na tabela ainda são 2 linhas separadas. Percebi que o PutDate não era o mesmo devido ao timestamp, então pensei com certeza que lançar para DATE resolveria, mas não resolveu.

script SQL:

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

Resultados na tabela:

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
sql-server insert

1 respostas

  1. Best Answer

    É uma coincidência que sua consulta funcione.

    Normalmente, você não pode usar um alias na GROUP BYcláusula de uma consulta - você precisa usar o nome real da coluna. Por exemplo, se você tentar executar esta consulta simples, receberá o erro Invalid column name 'ObjectName'ao tentar executar a consulta (observe que ela será analisada com sucesso, mas apenas erro na execução):

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

    Para corrigir isso, você usaria o nome da coluna sem alias ou adicionaria um CTE ou subconsulta para forçar a camada de abstração e fazer com que o mecanismo de consulta entendesse o alias.

    Em sua consulta, você usa coincidentemente o mesmo nome para a coluna fragmentada do OPENJSON...WITHe o alias calculado em sua seleção. Isso obscurece a questão real. Se você alterar o nome que está usando para sair da fragmentação JSON e a referência em seu SELECT, mas continuar usando o Alias ​​em seu GROUP BY, receberá uma Invalid columnmensagem de erro:

    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;

    Como aparte, agora que alteramos alguns dos rótulos, podemos ver que o equivalente funcional da sua consulta original é, na verdade, este:

    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;

    A maneira "rápida" de fazer isso funcionar não é usar o alias, e apenas usar o nome da coluna direta (neste caso, uma fórmula usando a CAST()função:

    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

    Pessoalmente, eu usaria um CTE para fragmentar o JSON em um formato tabular e, em seguida, construir minha consulta usando o CTE para fazer as agregações. Isso parece um pouco mais "limpo" para mim e me permite usar os nomes de alias onde eu quiser. Isso tem a vantagem de não ter que repetir chamadas de função e mantê-las em perfeita harmonia entre 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;

    Mas você também pode usar uma subconsulta em linha da mesma maneira.

    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;
    • 5

relate perguntas