[SQL] lastige geneste SELECT

[freeco]

Ik heb een lastige select proberen te maken, maar ik krijg het maar niet voor elkaar...
In woorden uitgelegd wil ik het volgende:

Ik wil een lijst met bestellingen en de artikels oproepen. Simpel dus? Ietsje moeilijker maken dan:
als een artikel 15x of meer voorkomt in 1 bestelling, mogen deze artikels niet voorkomen voor dit rapport.

Bijvoorbeeld:
Bestelling 1: 5x artikel X + 10x artikel Y
Bestelling 2: 10x artikel X + 15x artikel Z
Bestelling 3: 20x artikel Y + 50x artikel Z

Resultaat zou moeten zijn:
5 records artikel X uit bestelling 1
10 records artikel Y uit bestelling 1
10 records artikel X uit bestelling 2
rest moet geskipt worden

Verder dan het volgende kom ik niet:

code:

SELECT  ReferenceNbr, ArticleName
FROM    (SELECT   ReferenceNbr, ArticleName, COUNT(ArticleName) AS ArticleCount
              FROM      dbo.Stock
              WHERE  (ReferenceNbr LIKE '%45700%' OR ReferenceNbr LIKE '%45600%') AND 
                           (OrderDate BETWEEN '01/11/2002' AND '31/10/2003') AND 
                           (Company = 'BMB')
               GROUP BY ReferenceNbr, ArticleName) DERIVEDTBL
WHERE   (ArticleCount < 15)
ORDER BY ReferenceNbr, ArticleName

ReferenceNbr = nummer van de bestelling

Zo krijg ik een lijstje van bestellingen met de artikelnamen die minder dan 15x voorkomen.
Maar ik heb ook nog wat andere velden nodig zoals ArticleType, OrderDate, DeliveryDate, DeliveryAddress, etc
Al die velden zitten in tabel Stock, dus geen probleem voor joins of zoiets...

Eerder had ik met een collega ook al het volgende gevonden, maar dit geeft als resultaat dat wanneer 1 bestelling 15 artikels of meer bevat, de volledige bestelling wordt geskipt. We telden hier dus het aantal ReferenceNbrs, en niet de artikels. Beetje ingekort:

code:

SELECT  ArticleName, ArticleType, ...
FROM    (SELECT   *
              FROM      Stock
              WHERE  (ReferenceNbr IN
                             (SELECT  ReferenceNbr
                               FROM   (SELECT  ReferenceNbr, COUNT(ReferenceNbr) AS QTY
                                            FROM  Stock
                                            GROUP BY ReferenceNbr, ArticleNbr) DERIVEDTBL
                               WHERE  (QTY <= 15)))) DERIVEDTBL
WHERE     ...
ORDER BY OrderDate, ReferenceNbr, ArticleName

Op heb ook wat sites waar wat meer complexe syntax te vinden is, bekeken, maar ik zie niet onmiddellijk iets bruikbaars.
Met WHERE ... IN ( ... ) heb ik het ook geprobeerd, maar de syntax laat niet toe om én ReferenceNbr én ArticleName te gebruiken. Ik heb het dan geprobeerd met 2 IN's te koppelen met een AND, maar dit geeft ook niet het goeie resultaat.

Een tip welke functie of syntax ik verder zou kunnen gebruiken, zou meer dan welkom zijn!

[ Voor 3% gewijzigd door freeco op 14-11-2003 16:36 ]

[whoami]

Ik heb je complete query nog niet bekeken, maar ik zie al een eerste fout:

Als je filtert op aggregated columns (zoals die count), dan moet je dat niet in de WHERE maar in de HAVING clausule doen.

code:

SELECT naam, count(*) AS aantal
FROM tabel
GROUP BY naam
HAVING aantal < 15

dus.

[ Voor 21% gewijzigd door whoami op 14-11-2003 16:35 ]

[freeco]

Daar heb je waarschijnlijk wel gelijk in, qua performantie waarschijnlijk, maar het brengt me eigenlijk niet dichter bij een werkende query.
Ik had die HAVING al eerder geprobeerd, en achteraf in die WHERE gegooid. Het resultaat is gelijk.
Ik vermoed dat er nog een paar 'nestjes' nodig zijn om hier iets deftigs van te maken...

[ Voor 17% gewijzigd door freeco op 17-11-2003 11:32 ]

[whoami]

Dat heeft niks met performantie te maken.
Als je wilt 'filteren' op aggregated fields, dan moet je dat in de HAVING clause doen.

Geef eens je tabel-layout, dan kan men je trouwens beter helpen.

[JaQ]

Ik ga er voor het gemak maar even vanuit dat we het hier over oracle hebben. (aangezien je je databaast type niet hebt vermeldt)

een where op een geagregeerde kolom is gewoon semantisch fout. Of het op een andere manier werkt is niet zo interessant naar mijn mening, maar goed.

Ow.. trouwens.. die like's heb ik in dit geval vervangen door een substring om een aantal redenen:
1. de kans op foutieve selects is groter
2. einde oefening indexgebruik. Liever een substring, dan worden wel indexen gebruikt.

Anywayz, dat moet je uiteraard zelf weten.

Je subselect moet volgens mij dit zijn.

code:

SELECT ReferenceNbr
,      ArticleName
,      count(ArticleName) AS ArticleCount
FROM   dbo.stock
WHERE  substr(ReferenceNbr,1,5) IN ('45700', '45600') 
AND    OrderDate BETWEEN to_date('01-11-2002', 'DD-MM-YYYY') 
                 AND     to_date('31-10-2003', 'DD-MM-YYYY')
AND    Company = 'BMB'
HAVING ArticleCount < 15
GROUP BY
       ReferenceNbr
,      ArticleName

kan je je zo verder redden?

edit:

ow damn.. ik ben er nu vanuit gegaan dat orderdate een datumveld is, maar dat is altijd inclusief tijd in oracle, bij de "startdatum" moet er dus to_date('01-11-2002 00:00', 'DD-MM-YYYY HH24:MI') staan. bij de einddatum vervolgens to_date('31-10-2003 23:59')

en als het een echt sql rapport is, kijk een naar & of bind variables. dan kan je de nu hardgecodeerde waardes variabel maken.

[ Voor 21% gewijzigd door JaQ op 17-11-2003 11:52 ]

[freeco]

jaja, snap wat je wou bereiken: die nesting eruit halen. Is't nie?
Zo simpel is het niet hoor: door die COUNT moet ik dus werken met een GROUP BY, en aangezien ik een SELECT van 10 kolommen moet hebben, zou ik die 10 bij die GROUP moeten opgeven, en dan werkt de COUNT niet meer juist, of ik doe iets verkeerd
Ja, ben ook niet zo'n SQL-guru hoor. Kan me gewoon behelpen met het gemakkelijker stuff...

Ik zal eens proberen een overzichtje geven van de belangrijkste kolommen:
ID bigint 8 no nulls
ArticleNbr varchar 50
ArticleName varchar 100
OrderDate smalldate 4
DeliveryMfc smalldate 4
DeliveryEUS smalldate 4
SerialNbr varchar 50
ReferenceNbr varchar 50
DeliveryAddress varchar 50
WrtyEnd smalldate 4
WrtyType varchar 50
Status varchar 50
InstalledOn smalldate 4
MfcRef int 4
Company char 10
ArticleType char 20
...

46 kolommen in totaal, maar het is wel niet nodig om alles op te sommen zeker?

[JaQ]

daar gaat het niet om, de subselect die je maakte was gewoon een beetje krom. Je kan die query als subselect gebruiken,

dus:

code:

SELECT een_heleboel_kolommen
,      age_stk.ReferenNbr
,      age_stk.ArticleNbr
FROM   dbo.stock AS STK
,      (
        SELECT ReferenceNbr
        ,      ArticleNbr
        ,      count(ArticleName) AS ArticleCount
        FROM   dbo.stock
        WHERE  substr(ReferenceNbr,1,5) IN ('45700', '45600') 
        AND    OrderDate BETWEEN to_date('01-11-2002', 'DD-MM-YYYY') 
                         AND     to_date('31-10-2003', 'DD-MM-YYYY')
        AND    Company = 'BMB'
        HAVING ArticleCount < 15
        GROUP BY
               ReferenceNbr
        ,      ArticleNbr
       ) as AGE_STK
WHERE  age_stk.ReferenceNbr = stk.ReferenceNbr
AND    age_stk.ArticleNbr = stk.ArticleNbr
AND    en waar je verder nog op wilt joinen

ik snap het ontwerp van je tabel nog niet helemaal, lijkt een beetje krom, maar misschien komt dat door de hoeveelheid informatie.

[ Voor 8% gewijzigd door JaQ op 17-11-2003 11:59 ]

[freeco]

die is idd een beetje krom (ikke niet gemaakt )
nee serieus, dat komt omdat er gewoon van tijd tot tijd nieuwe noden ontstaan, en er kolommen worden aangeplakt... Om volledig opnieuw te beginnen, zou het teveel resources in beslag nemen.
Maar ik denk dat ik net een klein doorbraakje heb gemaakt. Eens zien of het resultaat goed is... Ik laat wel nog iets weten of het nu lukt. Toch al bedankt!

BTW: het gaat over een MS SQL server

[freeco]

yep, dat is em dus:

code:

SELECT  STK.ArticleName, STK.ArticleType, 
       STK.OrderDate, STK.DeliveryMfc, 
       STK.DeliveryAddress, STK.Status, 
       STK.DeliveryEUS, AGE_STK.ReferenceNbr, 
       STK.InstalledOn, STK.MfcRef
FROM Stock STK INNER JOIN
    (SELECT ReferenceNbr, ArticleNbr, COUNT(ArticleName) AS ArticleCount
     FROM stock
     WHERE (ReferenceNbr LIKE '%45700%' OR ReferenceNbr LIKE '%45600%') AND 
           (OrderDate BETWEEN '01/11/2002' AND '31/10/2003') AND 
           (Company = 'BMB') AND 
           (ZUCode = '') AND
           (MfcRef = '1')
     GROUP BY ReferenceNbr, ArticleNbr
     HAVING COUNT(ArticleName) < 15) AGE_STK ON 
           STK.ReferenceNbr = AGE_STK.ReferenceNbr AND 
           STK.ArticleNbr = AGE_STK.ArticleNbr
ORDER BY STK.ReferenceNbr, STK.ArticleName

tnx peeps

ps: de LIKE heb ik nog staan, maar eens zien wat de functie voor MS SQL is van die substr op de MSDN...