context: In SQL Server have a big XLSX file that i am mapping to an XML file. A column called 'referentie' gets different values each time. I need to count unique values and display a code accordingly . (Please note that is a subquery inside a bigger SELECT statement. I'm not able to add additional aggregate funcitons.)
- I need to count the unique values of a JSON array input (Output is in XML). (done)
- Display values depending on the retrieved unique count (issue)
issue : In the query you'll see i have multiple COUNT DISTINCT CASES. SQL throws error which is normal :
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
My query :
(SELECT COUNT(DISTINCT CASE WHEN Referentie > 10 THEN N'CON1' END) AS [HEADER/CHECK],
COUNT(DISTINCT CASE WHEN Referentie <= 10 AND Referentie > 29 THEN N'CON2' END) AS [HEADER/CHECK],
COUNT(DISTINCT CASE WHEN Referentie <= 30 AND Referentie >= 50 THEN N'CON3' END) AS [HEADER/CHECK],
COUNT(DISTINCT CASE WHEN Referentie <= 50 AND Referentie >= 500 THEN N'CON4' END) AS [HEADER/CHECK]
FROM OPENJSON(@Json, N'$.Blad1')
WITH (Referentie nvarchar(100) N'$.Referentie')) AS [HEADER/CHECK],
Question:
- What is the correct syntax here to make my logic work ?
JSON in file :
@Json = N'{
"Blad1":[
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"ZBRA6969860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":5.0,
"Barcode":"5414365359928",
"Artikel":"12481",
"Omschrijving":"meat! Kip & runderhuid sticks small 12,5cm - 100g - ± 9st",
"Hoeveelheid":2880.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":331.2,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G15A02"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":10.0,
"Barcode":"5414365341183",
"Artikel":"11953",
"Omschrijving":"Lam & Rijst Sticks 80gr - ± 20st",
"Hoeveelheid":1440.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":135.36,
"Intrastat":23091011.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G20B02"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"FLWU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":15.0,
"Barcode":"5414365357818",
"Artikel":"12452",
"Omschrijving":"meat! Eend jerkies 100g - ± 18st",
"Hoeveelheid":3600.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":414.0,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G29B02"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"JDCG2348657",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":20.0,
"Barcode":"5414365357900",
"Artikel":"12456",
"Omschrijving":"meat! Kippenfilets 100g - ± 4st",
"Hoeveelheid":2160.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":241.92,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G30B03"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":25.0,
"Barcode":"5414365357764",
"Artikel":"12450",
"Omschrijving":"meat! Kip & calcium benen 500g - ± 48st",
"Hoeveelheid":1320.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":792.0,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G29B04"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":30.0,
"Barcode":"5414365358389",
"Artikel":"12475",
"Omschrijving":"meat! Rijst benen met kip 140g - ± 4st",
"Hoeveelheid":960.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":144.96,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"5/H05A04"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":35.0,
"Barcode":"5414365358419",
"Artikel":"12476",
"Omschrijving":"meat! Rijst benen met eend 140g - ± 4st",
"Hoeveelheid":60.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":9.66,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"5/H03A01"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":40.0,
"Barcode":"5414365358099",
"Artikel":"12464",
"Omschrijving":"meat! Kip & kabeljauw hartjes 180g - ± 170st",
"Hoeveelheid":1280.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":248.32,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G19A03"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":45.0,
"Barcode":"5414365341275",
"Artikel":"11956",
"Omschrijving":"Eendenfilets 400gr - ± 80st",
"Hoeveelheid":864.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":368.928,
"Intrastat":23091011.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G20B03"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":50.0,
"Barcode":"5414365342852",
"Artikel":"11997",
"Omschrijving":"KIP & RUNDERHUID BENEN MEDIUM 1000g - ± 32st",
"Hoeveelheid":192.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":198.144,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G16B02"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":55.0,
"Barcode":"5414365363390",
"Artikel":"12650",
"Omschrijving":"meat! Kip & runderhuid bagels large 15-18cm - 225g/st - ± 9st",
"Hoeveelheid":72.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":162.0,
"Intrastat":23091090.0,
"Magazijn":"Wondelgem",
"Locatie":"5/H01A01"
},
{
"Bonnr.":42200420.0,
"Relatie":60840.0,
"Referentie":"TCNU4221860",
"Datum":"2021-07-26T00:00:00",
"Voorzien":"2022-02-08T00:00:00",
"Lijn":60.0,
"Barcode":"5414365341305",
"Artikel":"11957",
"Omschrijving":"meat! Kippenfilets 400gr - ± 15st",
"Hoeveelheid":288.0,
"Prijs":0.0,
"Korting 1":0.0,
"Korting 2":0.0,
"Korting 3":0.0,
"Netto":0.0,
"Waarde":0.0,
"Gewicht":122.976,
"Intrastat":23091011.0,
"Magazijn":"Wondelgem",
"Locatie":"3/G22B03"
}
]
}'
CodePudding user response：
Firstly, you are being somewhat unclear about what you are trying to achieve. You say you are managing to count unique values, but not display a value dependent on that.
Your code doesn't even manage that. What it actually does is check whether Referentie is within defined numbers (which makes no sense as it's a string), then distinct over a single string. Then it returns this multiple times, once for each parameter.
Instead, you need a single COUNT (DISTINCT Referentie), which has a CASE around it checking how much it is.
There is another issue with your current code. The conditions > and < are backwards. And < 29 should probably be < 30.
SELECT
'SomeValue' AS xyz,
(
SELECT
CASE
WHEN COUNT(DISTINCT Referentie) < 10
THEN N'CON1'
WHEN COUNT(DISTINCT Referentie) >= 10 AND COUNT(DISTINCT Referentie) < 29
THEN N'CON2'
WHEN COUNT(DISTINCT Referentie) >= 30 AND COUNT(DISTINCT Referentie) <= 50
THEN N'CON3'
WHEN COUNT(DISTINCT Referentie) >= 50 AND COUNT(DISTINCT Referentie) <= 500
THEN N'CON4'
END
FROM OPENJSON(@Json, N'$.Blad1')
WITH (
Referentie nvarchar(100)
) AS j
) AS [HEADER/CHECK]
FROM (VALUES(0)) DummyTable(DummyValue) -- don't know what the rest of your query is
FOR XML PATH(N''), ROOT(N'rootElement'),TYPE
As a side point, if you really wanted multiple columns from a subquery, you would need to place it in a APPLY.
CodePudding user response：
I've put the first query in a CTE and then your conditions in the query following but the results are probably not what you are expecting. Firstly Referentie is alpha-numerique so I have just used the numeric part, secondly only one of the values you are testing is met.
I have therefore grouped by Referntie and shown the number of duplicates to make things clearer while you are refining your query.
declare @json varchar(max); set @Json = ---- THE VALUE GIVEN IN THE QUESTION ---
WITH myJson as ( select right(Referentie,7) Referentie FROM OPENJSON(@Json, N'$.Blad1') WITH (Referentie nvarchar(100) ) AS [HEADER/CHECK] ) SELECT COUNT(*) "number", Referentie, COUNT(DISTINCT CASE WHEN Referentie > 10 THEN N'CON1' END) AS [HEADER/CHECK], COUNT(DISTINCT CASE WHEN Referentie <= 10 AND Referentie > 29 THEN N'CON2' END) AS [HEADER/CHECK], COUNT(DISTINCT CASE WHEN Referentie <= 30 AND Referentie >= 50 THEN N'CON3' END) AS [HEADER/CHECK], COUNT(DISTINCT CASE WHEN Referentie <= 50 AND Referentie >= 500 THEN N'CON4' END) AS [HEADER/CHECK] FROM myJson GROUP BY Referentie
GOnumber | Referentie | HEADER/CHECK | HEADER/CHECK | HEADER/CHECK | HEADER/CHECK -----: | :--------- | -----------: | -----------: | -----------: | -----------: 1 | 2348657 | 1 | 0 | 0 | 0 10 | 4221860 | 1 | 0 | 0 | 0 1 | 6969860 | 1 | 0 | 0 | 0
db<>fiddle here