[SQL] UPDATE met waarden en voorwaarden uit subqueries

Goede avond!

Ik zit even vast met een SQL query en zou wel een tip kunnen gebruiken om terug in de juiste richting te komen. Om de dienst accounting vooruit te helpen met het reconciliëren van een reeks financiële transacties, zijn er een hele reeks parameters waar rekening mee gehouden moet worden. Eén van die parameters zijn het aantal transacties die volgen op een initiële verkoop. Alle die losse transacties worden dagelijks afgeleverd in een vrij onoverzichtelijk bestand.

Om de accountants hun werk wat te vergemakkelijken werd beslist om de gegevens in Excel te behandelen, maar dat stootte onmiddellijk om een reeks problemen ivm de complexiteit van bepaalde excel functies, waardoor ze niet tot het gewenste resultaat kunnen komen.

Er werd me gevraagd om hiervoor een oplossing te bedenken in Excel, en ik ben de sheets gaan beschouwen als database tabellen. Er zit nu een vrij complex vba script achter al heel wat rapporten weet te generen mede dankzij SQL commandos.

Nu ben ik echter zelf tegen een probleempje opgebotst waar ik vandaag niet onmiddellijk een oplossing voor weet te vinden.

De hoofdtabel bestaat uit transacties zoals dit:
code:

[DATA]

SB    MYTEST    XXXXXXXXX            T0006    2012/03/01 23:24:31 +0100    2012/03/01 23:24:31 +0100    DR    8378    CHF                        
SB    MYTEST2        MYTEST    TXN    T0200    2012/03/01 23:24:31 +0100    2012/03/01 23:24:31 +0100    CR    6768    EUR                        
SB    MYTEST3        MYTEST    TXN    T2103    2012/03/01 23:40:23 +0100    2012/03/01 23:40:23 +0100    DR    6768    EUR                        
SB    MYTEST4        MYTEST    TXN    T1107    2012/03/02 23:40:23 +0100    2012/03/02 23:40:23 +0100    DR    6768    EUR                        
SB    MYTEST5    XXXXXXXXX            T0006    2012/03/01 23:24:31 +0100    2012/03/01 23:24:31 +0100    DR    8378    CHF

Dagelijks wordt die tabel bijgevuld met nieuwe transacties en er wordt dan ook een soort tijdelijke tabel gecreerd die de nieuwe transacties onderscheidt van de te updaten transacties.

De updates aan bestaan transactie bevatten bijvoorbeeld records zoals MYTEST4 voor de initiële transactie MYTEST.
code:

[Old_Transactions$]

MYTEST    MYTEST    2012/03/02 23:40:23 +0100
MYTEST5    MYTEST5    2012/03/03 23:40:23 +0100

Om bepaalde rapporten te genereren is het nodig om ten alle tijde het juiste aantal transacties, inclusief de initiële transactie bij te houden, omdat er enkele uitzonderingen niet mee mogen overgenomen worden, zoals wanneer men een T1107 transactie heeft en een transactie-count van 2.
code:

[TRANSACTIONS$]

9YW98011BE624690A    3
9YW99244C79293032    3
MYTEST            3
MYTEST5            1

In dit voorbeeld moet MYTEST 4 worden ipv 3. Hoe update ik dit?

Om het aantal transacties te tellen, gebruik ik dit SQL commando:
code:

SELECT DISTINCT C.[Transaction ID], 

((SELECT DISTINCT COUNT(B.[PayPal Reference ID]) FROM [Data$] B WHERE B.[PayPal Reference ID] = C.[Transaction ID]) + iif(isnull(C.[PayPal Reference ID]),1,0)) 

FROM [Data$] C 
INNER JOIN [Old_Transactions$] N 
ON (N.[transaction_id] = C.[Transaction ID])

WHERE C.[PayPal Reference ID] IS NULL

Het resultaat van dit commando is

MYTEST 3

Hoe kan ik via een SQL commando alle records in [Transactions$] update door een count te doen op het aantal transacties in [RAW] indien ze voorkomen in [Old_Transactions$] ?

Dit werkt zonder problemen, maar ipv '999' moet daar een count komen zoals in het SQL commando hierboven.
code:

UPDATE [Transactions$] SET transactions_count = '999' WHERE paypal_reference_id IN (SELECT DISTINCT transaction_id FROM [Old_Transactions$])

Ze combineren tot hetvolgende is natuurlijk fout en genereert een SQL error:
code:

UPDATE [Transactions$] 
SET transactions_count = 

(SELECT DISTINCT 
((SELECT DISTINCT COUNT(B.[PayPal Reference ID]) FROM [Data$] B WHERE B.[PayPal Reference ID] = C.[Transaction ID]) + iif(isnull(C.[PayPal Reference ID]),1,0)) as transactions_count
FROM [Data$] C 
INNER JOIN [Old_Transactions$] N 
ON (N.[transaction_id] = C.[Transaction ID])
WHERE C.[PayPal Reference ID] IS NULL)

WHERE paypal_reference_id IN (SELECT DISTINCT transaction_id FROM [Old_Transactions$])

Is er iemand die me een tip kan geven om me op weg te helpen?
Ik ben me ervan bewust dat Excel niet dient om SQL commando's te draaien, maar bij gebrek aan betere tools, moet het via deze weg.

Alvast bedankt!

Cheers,
Syaoran

Volgens mij heb je een interessante situatie daar qua onderhoud. Excel als databasetool, daar schijnt het natuurlijk niet voor gemaakt te zijn..

Wat voor database gaat het om? Welke exacte foutmelding krijg je? Naar iets als MySQL/SQL: Update with correlated subquery from the updated table itself gekeken?

Bedankt voor je reactie Pedorus.
De error die ik krijg is [Microsoft][ODBC Excel Driver] Operation must be an updateable query.
Ik zal vandaag nog verder zoeken als ik geen belangrijkere taken heb ^^

Die fout zou te maken hebben met ofwel een RO database (niet het geval) of het resultaat van die JOIN, volgens een KB die ik had gevonden. Maar hoe je het kon oplossen was niet gespecifieerd.

quote:

Hoshimaru schreef op maandag 05 maart 2012 @ 23:21:
Er werd me gevraagd om hiervoor een oplossing te bedenken in Excel, en ik ben de sheets gaan beschouwen als database tabellen.

Dat doen wel meer mensen, maar dat zijn het niet. Als je een database nodig hebt, gebruik dan ook een database.

quote:

farlane schreef op dinsdag 06 maart 2012 @ 18:28:
[...]


Dat doen wel meer mensen, maar dat zijn het niet. Als je een database nodig hebt, gebruik dan ook een database.

Wel, ik werk niet bepaald op de IT afdeling en met het huidige economische klimaat gaan ze zeker niet investeren in extra software dat hetgeen je nodig hebt om je taken uit te voeren en dus was Excel het beste alternatief. Zeer spijtige zaak, maar helaas niets aan te doen, ongeacht of iemand een achtergrond als IT'er heeft of niet.

Dat terzijde, ik ben tot een select statement gekomen dat die ODBC Excel Driver verstaat en hopelijk van toepassing is in een UPDATE statement.
code:

SELECT ((SELECT DISTINCT COUNT(B.[PayPal Reference ID])
FROM [Data$] B WHERE B.[PayPal Reference ID] = C.[Transaction ID]) + iif(isnull(C.[PayPal Reference ID]),1,0)) 
FROM [Data$] C
WHERE C.[PayPal Reference ID] IS NULL AND C.[Transaction ID] IN (SELECT [paypal_reference_id] FROM [Old_Transactions$])

Morgen zal 'k die query herschrijven om hem te kunnen gebruiken in een UPDATE-statemen, aangezien de subquery maar 1 records mag returnen en geen 2 zoals in dit voorbeeld het geval zou zijn.

quote: http://www.dbforums.com/m...-field-another-table.html

You cannot use SQL aggregate functions in an UPDATE query in Access, period. There is no way you can do it. As such, you have to either cache the data in a table, use a domain aggregate (DSUM etc) or write your own VBA UDF to aggregate the data.

COUNT gaat dus niet werken, en geeft net als een group by die foutmelding. En je moet sowieso opletten dat niet iets anders de lock heeft op je exceldocument, of dat je ReadOnly vergeten hebt te zetten, want dan krijg je exact dezelfde foutmelding om het makkelijk te maken..

Even wat snel getest, dcount werkt:
SQL:

1 2 3

UPDATE tab SET waarde = dcount('*', 'tab','naam="' & naam & '"') WHERE (naam = 'a')

(tab is hier een named range zodat je geen [Sheet$] hoeft te gebruiken)
Met een group by crashed VS trouwens, stabiele driver... Ook is er geen 64-bit versie.

Overigens zijn er prima gratis databases (oa mysql, postgresql, sql server express+sql management studio express).

Ik zal me eens verdiepen in DCOUNT, maar in mijn situatie zal dat waarschijnlijk niet helpen, want het lijkt er haast op de die 'database' engine niet om kan met subselects in een UPDATE statement.
SQL:

1 2 3 4 5 6

UPDATE [Transactions$] T SET T.[transactions_count] =  (     SELECT [paypal_reference_id] FROM [Transactions$] WHERE [paypal_reference_id] = T.[paypal_reference_id] )  WHERE T.[paypal_reference_id] IN (SELECT O.[paypal_reference_id] FROM [Old_Transactions$] O WHERE O.[paypal_reference_id] = 'MYTEST')

Zou exaxt 1 record moeten teruggeven, namelijk 'MYTEST' en toch krijg ik steeds diezelfde error van in mijn vorige post

Een andere oplossing zou zijn om die counts gewoon te generen tijdens de INSERT van data in die [Old_Transactions] tabel waardoor ik simpelweg moet verwijzen naar een veld. Misschien is dat gewoon dé oplossing voor dat Excel geklooi.
Ik heb er even niet bij stilgestaan dat die [Old_Transactions] almoet bestaan om de COUNT te kunnen uitvoeren... Dat wordt dan nog een extra sheet-tabel met 2 velden: transaction_id en updated_count_value of zoiets

Een echte database krijg ik dus niet van IT, ook al is het opensource, en ik mag er ook zelf geen lokaal installeren. Ik kan het zelfs niet eens door beperkte user rights op m'n client *zucht*

Ze hebben zelfs alle Access installaties verwijderd bij iedereen die het had omdat er teveel 'problemen' zouden ontstaat door al die kleine databases die hier en daar gebruikt worden. Die mensen hun tabellen zijn ook allemaal naar Excelsheets gedumpt en ze moeten maar zien wat ze ermee kunnen (niks dus)

Update:
Om niet teveel tijd te verliezen heb ik voor een workaround gekozen:
SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13

DROP TABLE Old_Transactions_Count CREATE TABLE Old_Transactions_Count (transaction_id TEXT, transactions_count NUMBER)  INSERT INTO [Old_Transactions_Count$] (transaction_id , transactions_count) SELECT DISTINCT C.[Transaction ID], ((SELECT DISTINCT COUNT(B.[PayPal Reference ID]) FROM [Data$] B WHERE B.[PayPal Reference ID] = C.[Transaction ID]) + iif(isnull(C.[PayPal Reference ID]),1,0))  FROM [Data$] C WHERE C.[PayPal Reference ID] IS NULL AND C.[Transaction ID] IN (SELECT [paypal_reference_id] FROM [Old_Transactions$]) UPDATE [Transactions$] T INNER JOIN [Old_Transactions_Count$] TC  ON T.[paypal_reference_id] = TC.[transaction_id] SET T.[transactions_count] = TC.[transactions_count]

Die [Transactions], [New_Transactions], [Old_Transactions] en [Old_Transactions_Count] moeten toch niet zichtbaar zijn voor de eindgebruikers, dus zo erg is dat niet lijkt me. Het lijkt me een aanvaardbare oplossing voor dit probleem als je die Old_Transactions_Count als een TEMP TABLE of VIEW beschouwt.

Hoshimaru wijzigde deze reactie 07-03-2012 16:49 (31%)
Reden: Workaround

quote:

Hoshimaru schreef op woensdag 07 maart 2012 @ 16:15:
Zou exaxt 1 record moeten teruggeven, namelijk 'MYTEST' en toch krijg ik steeds diezelfde error van in mijn vorige post

Subqueries zijn ook niet toegestaan, zie http://stackoverflow.com/...y-with-subquery-in-access en gelinkte ACC: Update Query Based on Totals Query Fails

quote:

Een echte database krijg ik dus niet van IT, ook al is het opensource, en ik mag er ook zelf geen lokaal installeren. Ik kan het zelfs niet eens door beperkte user rights op m'n client *zucht*

Ze hebben zelfs alle Access installaties verwijderd bij iedereen die het had omdat er teveel 'problemen' zouden ontstaat door al die kleine databases die hier en daar gebruikt worden. Die mensen hun tabellen zijn ook allemaal naar Excelsheets gedumpt en ze moeten maar zien wat ze ermee kunnen (niks dus)

Briljant.

quote:

Hoshimaru schreef op woensdag 07 maart 2012 @ 16:15:

Een echte database krijg ik dus niet van IT, ook al is het opensource, en ik mag er ook zelf geen lokaal installeren. Ik kan het zelfs niet eens door beperkte user rights op m'n client *zucht*

Ze hebben zelfs alle Access installaties verwijderd bij iedereen die het had omdat er teveel 'problemen' zouden ontstaat door al die kleine databases die hier en daar gebruikt worden. Die mensen hun tabellen zijn ook allemaal naar Excelsheets gedumpt en ze moeten maar zien wat ze ermee kunnen (niks dus)

Volgens mij heb je helemaal geen technisch probleem
Laat je baas eens aan de bel trekken bij die clowns!

Gekker kan het niet hé :3

Maar goed, de workaround zorgt voor het gewenste resultaat. Morgen de controles afwerken die zullen verhinderen dat iemand data dubbel gaat importeren in [Data$]

Ik denk dat ik voor even geen zotte SQL statements meer nodig zal hebben, totdat de data provider over de boeg komt met de exacte informatie over transacties die we tot nu nog niet hebben gekregen. Oa. details over transactiekosten, commissies afhankelijk van de gebruikte munt, reserves, geblokeerde waarborgen etc. Die dingen beïnvloeden ronduit alles en maken het ontzettend moeilijk voor mijn collega' som de betalingen te reconciliëren zolang die dingen onbekend blijven.

Voor de rest zijn er voorlopig gewoon wat rapportering en berekeningen. De grootste zorg nu is die VBA fool-proof maken.

Bedankt voor jullie hulp en tips

Ziezo

De eerste release van het tooltje is af. Ik heb een aantal queries wel moeten opsplitsen en gebruik maken van enkele hulpsheets/tabellen om te verhinderen dat een gebruiker 2 minuten naar zijn scherm moet staren om gewoon een rapportje te verkrijgen, zoals met dit:
SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SELECT s.[Transaction ID], s.[Invoice ID], CLng(Format(IIF(r.[Transaction Event Code] = 'T1107', '-' & r.[Gross Transaction Amount], r.[Gross Transaction Amount]),'#0.00'))/100 as [Gross Transaction Amount], r.[Gross Transaction Currency], CLng(Format(IIF(r.[Transaction Event Code] = 'T1107', '-' & s.[Fee Amount] , s.[Fee Amount]),'#0.00'))/100 AS [Fee Amount], s.[Fee Currency], r.[Transaction Event Code], s.[Transaction Completion Date] , r.[Transaction Completion Date]  FROM [Data$] s  INNER JOIN [Data$] r  ON (s.[Transaction ID] = r.[PayPal Reference ID])  WHERE s.[Transaction ID] NOT IN  (SELECT DISTINCT a.[PayPal Reference ID] FROM [Data$] a INNER JOIN [Transactions$] b ON (a.[PayPal Reference ID] = b.[paypal_reference_id]) WHERE EXISTS (SELECT DISTINCT s1.[PayPal Reference ID]  FROM [Data$] s1  WHERE s1.[Transaction Event Code] = 'T1107' AND a.[PayPal Reference ID] = s1.[PayPal Reference ID]) AND NOT EXISTS (SELECT DISTINCT s2.[PayPal Reference ID]  FROM [Data$] s2  WHERE s2.[Transaction Event Code] = 'T2104' AND a.[PayPal Reference ID] = s2.[PayPal Reference ID]) AND a.[Transaction Completion Date] LIKE '2012/01/26%' ) AND r.[Transaction Completion Date] LIKE '2012/01/26%'  AND (r.[Transaction Event Code] = 'T2104' OR r.[Transaction Event Code] = 'T1107')  AND r.[Gross Transaction Currency] = 'EUR'  ORDER BY r.[Transaction Event Code] DESC

Nu duurt dat gemiddeld 2 seconden en hooguit 40 seconden indien er echt héél veel gegevens opgevraagd worden

Het importeren van csv bestanden in de database duur wel een minuutje, maar er wordt wel iedere keer een stats tabel geupdate en/of aangevuld afhankelijk van de gegevens in de csv. Zonder die operaties per file duurt het rebuilden van de stats tabel zo'n 17minuten momenteel, tegen 14 financiële transacties per seconde. Wel bestaat de mogelijkheid nog om die te regenereren als er echt iets moest zijn fout gelopen.

Sorry, maar ik kom toch niet echt uit met het de-installeren van Access.
Hoe haalt iemand (een verantwoordelijke ergens in de ICT\ITIL structuur) het in zijn hoofd om die "oplossing" te accepteren.
Dat er geen geld is ok, maar een bestaande service slopen om een andere te repareren die problemen geeft doordat het wordt misbruikt? Dat slaat echt helemaal nergens op (uiteraard ken ik de exacte situatie niet).

Wel... De luchtvaarmaatschappij waar ik werk heeft vorig jaar zijn migratie naar Windows 7 laten uitvoeren voor een van de grotere Indisch IT consultancy bedrijven, maar tussen die 'consultants' waren er vrij veel die hun diploma in een Kinder Surprise eitje hebben gevonden.

Dat het een en ander niet gaat omdat er in de luchtvaart vaak nog legacy software nodig is, kan ik begrijpen, maar dat van Access niet echt. Alle programma's die daarvan afhankelijk waren werken niet meer omdat ze er niet in waren geslaagd om oa een Access 95 databases met haar ondersteunende applicatie te converteren of bruikbaar te maken voor Windows 7.

Ze hebben toen een App-V server geïnstalleerd om die oudere dingen te doen draaien, maar dat lukte hen niet zo goed, waarna ze hadden gezegd dat dat niet ging en ze er teveel moeite voor moesten doen moest verdwijnen... Die kerels zijn trouwens 2 maanden langer gebleven dan aanvankelijk gepland.

Pikant detail: de tool met VMware ThinApp deployen werkte als een fluitje van een cent (zelf getest en voorgesteld adv TCO van toepassing op onze situatie etc), maar daar wilden die Indiërs niets van weten, op eentje na, maar die kreeg naar zijn voeten van zijn baas...