Case met max

Status
Niet open voor verdere reacties.
R

RolandPricken

Gebruiker
Lid geworden
17 sep 2010
Berichten
49
Hallo, ik heb de volgende query:


SELECT
ITEMID, ITEMNAME, NEW_MINONHAND, NEW_MAXONHAND, AVAILPHYSICAL, WMSLOCATIONID

Echter wil ik graag dat de NEW_MAXONHAND wordt weer weergeven, anders 0 als de AVAILPHYSICAL maximaal is per itemid.

Anders verwoord, als de AVAILPHYSICAL maximaal is per itemid dient de NEW_MAXONHAND weergegeven te worden, indien de AVAILPHYSICAL niet maximaal per itemid dient er 0 weergegegeven te worden.


Nu heb ik deze case gebruikt, dit werkt natuurlijk niet. Ik heb diverse variaties geprobeerd hierop, o.a. ook de ">= 0" te vervangen door AVAILPHYSICAL maar dit werkt ook niet.

CASE WHEN MAX(dbo.INVENTSUM.AVAILPHYSICAL) >= 0 THEN dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND ELSE 0 END AS Expr1

Kan iemand me hier mee helpen?

Alvast bedankt
 
Wat gaat er precies fout? Hoe ziet de rest van de query eruit? Soms kun je extra dingen doen met een "group by" en "Having".

Om een case te kunnen gebruiken moet de informatie beschikbaar zijn op resultaat niveau. Ik kan niet goed inschatten of dat ook het geval is.
 
Dit is de hele query:

SELECT DISTINCT
TOP (100) PERCENT dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.ITEMNAME, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND)
AS NEW_MINONHAND, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND) AS NEW_MAXONHAND, CONVERT(int, dbo.INVENTSUM.AVAILPHYSICAL)
AS AVAILPHYSICAL, dbo.INVENTDIM.WMSLOCATIONID, (CASE WHEN (dbo.INVENTDIM.WMSLOCATIONID NOT LIKE '%HTC.%') THEN (CONVERT(int,
dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND)) ELSE 0 END) AS NEW_MINONHAND1, (CASE WHEN (dbo.INVENTDIM.WMSLOCATIONID NOT LIKE '%HTC.%')
THEN (CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND)) ELSE 0 END) AS NEW_MAXONHAND1
FROM dbo.INVENTTABLE INNER JOIN
dbo.INVENTITEMLOCATION ON dbo.INVENTTABLE.ITEMID = dbo.INVENTITEMLOCATION.ITEMID AND
dbo.INVENTTABLE.DATAAREAID = dbo.INVENTITEMLOCATION.DATAAREAID INNER JOIN
dbo.DYN_MINMAXPROPOSALS ON dbo.INVENTTABLE.DATAAREAID = dbo.DYN_MINMAXPROPOSALS.DATAAREAID AND
dbo.INVENTTABLE.ITEMID = dbo.DYN_MINMAXPROPOSALS.ITEMID INNER JOIN
dbo.INVENTSUM ON dbo.INVENTITEMLOCATION.DATAAREAID = dbo.INVENTSUM.DATAAREAID AND
dbo.INVENTITEMLOCATION.ITEMID = dbo.INVENTSUM.ITEMID INNER JOIN
dbo.INVENTDIM ON dbo.INVENTSUM.DATAAREAID = dbo.INVENTDIM.DATAAREAID AND dbo.INVENTSUM.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID
WHERE (dbo.INVENTITEMLOCATION.WMSPICKINGLOCATION LIKE 'HTC.%') AND (NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'rw')) AND
(NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'repair')) AND (NOT (dbo.INVENTDIM.INVENTLOCATIONID LIKE 'voda-%')) AND
(NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'spwh')) AND (NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'htc')) AND (dbo.INVENTDIM.WMSLOCATIONID <> '') AND
(NOT (dbo.INVENTDIM.INVENTLOCATIONID LIKE 'mw'))
ORDER BY dbo.INVENTTABLE.ITEMID


Geen group by en geen having dus

mdhZ3.png


Zoals je ziet, eerste 2 regels, een itemid met 2 wmslocationids, een keer J.03.11 en HTC.106. Hier hoort ook een min en max bestelgrootte bij (middelste kolommen), echter nu geeft hij bij beide itemid dezelfde min en max aan. Vandaar de 2 kolommen rechts, dit zijn de cases. Nu is de case zo dat hij kijkt naar de wmslocationid, begint die met HTC dan dienen de nieuwe min en max nul te zijn, anders de waarde uit de min of max kolom in het midden. Meestal klopt dit ook, maar niet altijd.
Daarom wil ik het dat naar de availphysical (rood gekleurd) kijkt, waar deze het hoogste is bij 1 van de 2 itemid's, daar moet hij de min of max uit de middelste kolom geven. Anders 0.

Ik hoop dat het nu iets duidelijker is.
 
Ik denk dat ik het begrijp. voor de twee aangeven voorbeelden wil je dus de waarden behorend bij 935 en 162 respectievelijk?

als in beide gevallen availphysical 0 is wil je 0,0 ?

wil je nog wel beide lijnen in je output? of alleen de relevante lijn? als je alleen de relevante lijn wil is dit eenvoudig met een group clausule.
 
wampier zei:
Ik denk dat ik het begrijp. voor de twee aangeven voorbeelden wil je dus de waarden behorend bij 935 en 162 respectievelijk?

als in beide gevallen availphysical 0 is wil je 0,0 ?

wil je nog wel beide lijnen in je output? of alleen de relevante lijn? als je alleen de relevante lijn wil is dit eenvoudig met een group clausule.
Klik om te vergroten...

Klopt, bij het eerste itemid en het tweede dient de waarde van de laatste en voorlaatste kolom gebaseerd te zijn op 935 en 162. Oftewel waar deze waarde (availphysical) het hoogste is. Availphysical zal nooit 0 bij beide itemid zijn, deze heeft altijd voorraad, is het niet op locatie 1, dan is het wel op 2.

Beide lijnen is niet nodig. De laatste 2 kolommen zijn genoeg, de middelste 2 met dezelfde namen kunnen weg.

Nog even wat ik wil:

Kolom 1: itemid, de namen van de item. items ligggen op meerdere locaties zoals te zien is in de kolom wmslocationid. ieder itemid heeft voorraad, soms op 2 plaatsten, soms maar op 1. Ook heeft ieder itemid heeft een min en max, kolom 3 & 4. Echter krijgt nu ieder itemid deze min en max op beide locaties. Nu wil ik dat in de laatste 2 kolommen de min en max weergeven als aan deze voorwaarde voldaan wordt. De min en max bij een itemid moet worden weergegeven bij de regel waar de voorraad (availphysical) het hoogste is, bij alle andere locatie behorend bij het itemid 0. Dus in het geval van itemid 72H06591-00M is de voorraad op locatie J.03.11.11 het hoogste, daar dient een min van 3597 en een max van 5396 weergeven te worden, bij locatie HTC.106 dient bij beide kolommen min en max 0 weergegeven te worden.
Bij 76H05683-00M is de voorraad op locatie HTC.317 het hoogste, er dient in de laatste 2 kolommen, min en max, de waarde 166 en 223 weergegeven, op locatie H.04.11.51 van item 76H05683-00M dient bij de nieuwe kolommen 0 en 0 weergegeven te worden.

Nu is de case gebaseerd op de locatie, dit dient dus op availphysical te zijn.
 
n dat geval kun je een

[sql]... MAX(availphysical) AS beschikbaar CASE when beschikbaar > 0.... END ... GROUP BY itemid [/sql]

je krijgt dan:

72H06591-00M gasket 3597 5396 935 0.03.11.11 3597 5396
76H05683-00M mylar 166 233 162 HTC.317 166 233
etc.

1 regel per itemid. de MAX functie kiest de hoogste leverwaarde en is via "beschikbaar" te gebruiken in je CASE statement
 
De select en group by ziet er nu zo uit:

SELECT DISTINCT
TOP (100) PERCENT dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.ITEMNAME, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND)
AS NEW_MINONHAND, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND) AS NEW_MAXONHAND, CONVERT(int, dbo.INVENTSUM.AVAILPHYSICAL)
AS AVAILPHYSICAL, dbo.INVENTDIM.WMSLOCATIONID, MAX(dbo.INVENTSUM.AVAILPHYSICAL) AS Expr1 case when Expr1 > 0 end
GROUP BY dbo.INVENTTABLE.ITEMID


Ik doe ik verkeerd, ik krijg geen resultaten, ook niet met variaties
 
wat krijg je zonder de case en group by? ik zie dat je een aantal expliciete converts doet. levert MAX(dbo.INVENTSUM.AVAILPHYSICAL) AS Expr1 wel een geldig resultaat op?
 
Nee, alleen maar foutmelding, afhankelijk van de variatie in de select (koma, haakje, etc) herkent hij de case of as niet.
 
als je alles zoals vroeger laat behalve de toegevoegde MAX gaat het dan goed? mogelijk is er een conflict ergens. Als het goed is krijg je een extra kolom met een vaste hoge waarde voor alle entries.

wat als je de MAX uitvoerd op AVAILPHYSICAL en niet dbo.INVENTSUM.AVAILPHYSICAL ?
 
ik kan de max wel aan de query toevoegen, indien ik de zaken de select in de group by zet. Dan krijg ik:

SELECT DISTINCT
TOP (100) PERCENT dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.ITEMNAME, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND)
AS NEW_MINONHAND, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND) AS NEW_MAXONHAND, CONVERT(int, dbo.INVENTSUM.AVAILPHYSICAL)
AS AVAILPHYSICAL, dbo.INVENTDIM.WMSLOCATIONID, MAX(dbo.INVENTSUM.AVAILPHYSICAL) AS Expr1
FROM dbo.INVENTTABLE INNER JOIN
dbo.INVENTITEMLOCATION ON dbo.INVENTTABLE.ITEMID = dbo.INVENTITEMLOCATION.ITEMID AND
dbo.INVENTTABLE.DATAAREAID = dbo.INVENTITEMLOCATION.DATAAREAID INNER JOIN
dbo.DYN_MINMAXPROPOSALS ON dbo.INVENTTABLE.DATAAREAID = dbo.DYN_MINMAXPROPOSALS.DATAAREAID AND
dbo.INVENTTABLE.ITEMID = dbo.DYN_MINMAXPROPOSALS.ITEMID INNER JOIN
dbo.INVENTSUM ON dbo.INVENTITEMLOCATION.DATAAREAID = dbo.INVENTSUM.DATAAREAID AND
dbo.INVENTITEMLOCATION.ITEMID = dbo.INVENTSUM.ITEMID INNER JOIN
dbo.INVENTDIM ON dbo.INVENTSUM.DATAAREAID = dbo.INVENTDIM.DATAAREAID AND dbo.INVENTSUM.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID
WHERE (dbo.INVENTITEMLOCATION.WMSPICKINGLOCATION LIKE 'HTC.%') AND (NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'rw')) AND
(NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'repair')) AND (NOT (dbo.INVENTDIM.INVENTLOCATIONID LIKE 'voda-%')) AND
(NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'spwh')) AND (NOT (dbo.INVENTDIM.WMSLOCATIONID LIKE 'htc')) AND (dbo.INVENTDIM.WMSLOCATIONID <> '') AND
(NOT (dbo.INVENTDIM.INVENTLOCATIONID LIKE 'mw'))
GROUP BY dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.ITEMNAME, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND), CONVERT(int,
dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND), CONVERT(int, dbo.INVENTSUM.AVAILPHYSICAL), dbo.INVENTDIM.WMSLOCATIONID
 
Maar krijg je nu ook de verwachte output? als het zo wel werkt moet je nu je case toe kunnen voegen.
 
Ik krijg dan als output een extra kolom met dezelfde waardes als in de kolom availphysical, vrij voor de hand liggend. Het aantal records blijft logischerwijs ook hetzelfde.
Maar hoe zou ik dan de case moeten maken? Ik kan deze er niet simpelweg achter plaatsten....., Ik creeër namelijk expr1, hier kan ik niet direct weer een case van maken, toch?

Onderstaande qry werkt dus niet.

SELECT DISTINCT
TOP (100) PERCENT dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.ITEMNAME, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND)
AS NEW_MINONHAND, CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MAXONHAND) AS NEW_MAXONHAND, CONVERT(int, dbo.INVENTSUM.AVAILPHYSICAL)
AS AVAILPHYSICAL, dbo.INVENTDIM.WMSLOCATIONID, MAX(dbo.INVENTSUM.AVAILPHYSICAL) AS Expr1, CASE WHEN Expr1 > 0 THEN CONVERT(int,
dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND) else 0 END AS Expr2
 
Dat is afhankelijk van de engine, anders:

[sql]CASE WHEN MAX(dbo.INVENTSUM.AVAILPHYSICAL) > 0 THEN CONVERT(int, dbo.DYN_MINMAXPROPOSALS.NEW_MINONHAND) else 0 END[/sql]
 
Hij doet het nu wel maar geeft niet de goede resultaten:

mozDH.png


Edit: hij houdt geen rekening met itemid
 
Laatst bewerkt:
Wat had je dan graag gezien? Ik zie vier ID's met 4 verschillende waarden.
 
Je ziet dat er 2 gelijke itemid's zijn, hij moet die case doen op die 2, niet op iedere regel.

in het geval van de 1ste itemid, is availphysical bij locatie J.03.11.31 het hoogste, daar klopt 879, maar bij de regel bij HTC.221 moet het dus 0 zijn en niet 879
 
Waarom zou je daar "0" verwachten? het max van dat ID is groter dan 0, dus wordt dat op beiden toegepast. Als je alleen een waarde bij de hoogste waarde wilt moet je natuurlijk je CASE aanpassen om max te vergelijken met de available per lijn.
 
Ik wil inderdaad de hoogste waarde per set (in dit geval 2) itemid. Ik ben schijnbaar niet duidelijk genoeg geweest.

Hoe ziet de case er dan uit?
 
De meeste verwarring is denk ik over het feit dat de andere 0 moet zijn.

even uit de losse pols:
[sql]case when MAX(dbo.INVENTSUM.AVAILPHYSICAL) = availphysical then minmax else 0 end[/sql]
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan