Migratie van Access query naar SQL query - juiste syntax ?

Status
Niet open voor verdere reacties.

leifoet

Gebruiker
Lid geworden
7 okt 2007
Berichten
326
Probeer een Access query (met subquery) te converteren naar SQL

De query in Access
Code:
SELECT Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam, 
Sum(RCdetails18.BUitAKP) AS TBUitAKP, 
Sum(RCdetails18.BInVKP) AS TBInVKP, 
Sum(RCdetails18.BInVKP)-Sum(RCdetails18.BUitAKP) AS SaldoRC, 
IIf(Sum(RCdetails18.BInVKP)-Sum(RCdetails18.BUitAKP)>0,0,Sum(RCdetails18.BInVKP)-Sum(RCdetails18.BUitAKP)) AS SaldoDT, 
IIf(Sum(RCdetails18.BInVKP)-Sum(RCdetails18.BUitAKP)<0,0,Sum(RCdetails18.BInVKP)-Sum(RCdetails18.BUitAKP)) AS SaldoCT
FROM Leden 
INNER JOIN RCdetails18 ON Leden.[Leden-Id]=RCdetails18.ledenid
WHERE (((RCdetails18.Datum)<=[datum?]))
GROUP BY Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam
ORDER BY Leden.LedenNr;

De subquery (RCdetails18) in Access
Code:
SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_A18
UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_B18;

Mijn conversie
Code:
sql = "SELECT Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam, "&_ 
"SUM((SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).BUitAKP) AS TBUitAKP, "&_
"SUM((SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).BInVKP) AS TBInVKP, "&_
"Sum((SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).BInVKP) "&_
"-SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).BUitAKP) AS SaldoRC, "&_ 

"CASE WHEN SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BInVKP]"&_
"-SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BUitAKP]>0 THEN 0 "&_
"ELSE SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BInVKP]"&_
"-SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BUitAKP]) AS SaldoDT, "&_

"CASE WHEN SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BInVKP]"&_
"-SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BUitAKP]<0 THEN 0 "&_
"ELSE SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BInVKP]"&_
"-SUM(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).[BUitAKP]) AS SaldoCT "&_

"FROM Leden "&_
"INNER JOIN (SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18) "&_
"ON Leden.[Leden-Id]=(SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).ledenid "&_
"WHERE (((SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).Datum)<=[datum?])) "&_
"GROUP BY Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam "&_
"ORDER BY Leden.LedenNr"

Vraag :
- Sum(RCdetails18.BUitAKP) : combinatie subquery en veld => correct omzetting ?
- IIF : = correct omgezet ?
- Datum)<=[datum?])) : (hoe) kan deze code omgezet worden ?

Dank voor tips.
 
Parameters zijn in een SQL server omgeving niet te gebruiken, tenzij je er een Stored Procedure van maakt. Van andere SQL systemen heb ik te weinig kaas gegeten om er wat van te zeggen. Maar eigenlijk zijn de meeste systemen veel minder flexibel als wat je nu in Access hebt. Vraag is natuurlijk: waarom zet je de db om naar SQL server, en wil je Access niet meer gebruiken?
 
Omzetting naar SQL -onder andere- om medeleden die niet over Access beschikken bepaalde modules te laten gebruiken.

Heb intussen de 3de laatste zin uit 'mijn conversie' voorlopig verwijderd :
"WHERE (((SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP FROM tabl_B18).Datum)<=[datum?])) "&_

Krijg nu deze error :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in union query.

Waar zoek ik (mogelijks) deze syntaxfout ?
Dank voor tips.
 
Laatst bewerkt:
Naar mijn mening moet je [datum?] buiten de quotjes houden om gebruik te kunnen maken van de waarde.
Nu gebruik je het gewoon als tekst in je query.

Volgens mij...

Frank
 
Had de volledige codelijn m.b.t. [datum?] al geschrapt (in voorgaande mail).

Vraag blijft : waarom krijg ik 'Syntax error in union query' ?
Dank voor tips.
 
Hey,

Ik doe mijn best om de bedoeling te begrijpen, dus weet niet of dit gaat werken.
Maar, ik zou....

Deze zo laten en een view van maken. ( 1x executen )
Code:
CREATE VIEW VW_RCdetails18 AS
SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_A18
UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_B18

Als het goed is heb je nu een nieuwe view in de DB.

Deze view vervolgens gebruiken voor de join.
Case toepassen ipv Iif (geen idee of Iif ook werkt)
Datum tussen de quotjes uit

Code:
SQL="SELECT Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam, "&_
"Sum(VW_RCdetails18.BUitAKP) AS TBUitAKP, "&_
"Sum(VW_RCdetails18.BInVKP) AS TBInVKP, "&_
"Sum(VW_RCdetails18.BInVKP)-Sum(VW_RCdetails18.BUitAKP) AS SaldoRC, "&_ 
"CASE WHEN (Sum(VW_RCdetails18.BInVKP)-Sum(VW_RCdetails18.BUitAKP)>0 THEN 0 ELSE Sum(VW_RCdetails18.BInVKP)-Sum(VW_RCdetails18.BUitAKP)) END AS SaldoDT, "&_   
"CASE WHEN (Sum(VW_RCdetails18.BInVKP)-Sum(VW_RCdetails18.BUitAKP)<0 THEN 0 ELSE Sum(VW_RCdetails18.BInVKP)-Sum(VW_RCdetails18.BUitAKP)) END AS SaldoCT  "&_
"FROM Leden  "&_
"INNER JOIN VW_RCdetails18 ON Leden.[Leden-Id]=VW_RCdetails18.ledenid "&_
"WHERE (((VW_RCdetails18.Datum)<=#"& [datum?] &"#)) "&_
"GROUP BY Leden.[Leden-Id], Leden.LedenNr, Leden.Familienaam, Leden.Voornaam "&_
"ORDER BY Leden.LedenNr; "


Het zal vast ook in 1 query kunnen, maar ik krijg dat niet voor elkaar.

Ik hoop dat je er wat mee kan.


Frank
 
Laatst bewerkt:
Vooraf : de CREATE VIEW opdracht was mij niet bekend.
Bij het uitvoeren van de code ervan krijg ik volgende error (bij CREATE VIEW VW_RCdetails18 AS)
Microsoft VBScript compilation error '800a0401'
Expected end of statement

Heb ik soms iets over het hoofd gezien ?
 
Hey,

Ok dat was even te kort door de bocht.

De Create View is eenmalig en voer je normaal op de (SQL)server uit. (tenminste...ik doe dat)
Als je de Create View uit dezelfde omgeving wil uitvoeren als de SELECT query moet deze nog tussen quotjes.

Sorry als ik niet duidelijk was.

Frank
 
Laatst bewerkt:
Een aantal pogingen tot inlassingen van quotjes (o.a. hieronder) elimineren de error (nog) niet => vraag : waar exact moeten de quotjes ingelast worden in deze code ?
CREATE "VIEW VW_RCdetails18 AS" &_
"SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_A18 UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_B18"

Create view is éénmalig - komt er dan toch een fysische tabel bij ? of wat wordt bedoeld met éénmalig ?
Waar plaats ik dan SQL="SELECT Leden … " als CREATE VIEW éénmalig is ?

Zoals ik schreef is deze combinatie (nog) onontgonnen terrein.
Dank voor verduidelijking.
 
Code:
CREATE VIEW VW_RCdetails18 AS
SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_A18
UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_B18

Dit is in zijn geheel een SQL statement.

Gebruik je éénmalig om in de DB een view te maken.
Dit is zeg maar een permanente query die je kan aanspreken als (virtuele) tabel.
Nogmaals, normaal zou ik zoiets in een query op de DB draaien.

De select komt gewoon in je code waar nu die ook staat.

Wat voor DB / Server / Tool gebruik je voor de SQL DB ? en waar heb je de code in staan ? (BV MySQL server als DB en Acces of VB als front end)

Frank
 
ASP+Access-DB (dus Microsoft/Windows server)

Vind in de herhaalde CREATE VIEW-code geen quotes ?

Gebruik je éénmalig om in de DB een view te maken.
Dit is zeg maar een permanente query die je kan aanspreken als (virtuele) tabel.
Moet deze VIEW-query dan als een afzonderlijke / nieuwe module slechts één keer (eenmalig) 'draaien' en -nadien- telkens alléén de module met de 'gewone' SELECT-code ? of toch beide samen ?
 
Laatst bewerkt:
Moet deze VIEW-query dan als een afzonderlijke / nieuwe module slechts één keer (eenmalig) 'draaien' en -nadien- telkens alléén de module met de 'gewone' SELECT-code

Yep.
 
In de (hoofd)sql CASE vervangen door IIF (CASE gaf error => is geen 'Access-DB-commando').

Krijg nu deze error
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(((VW_RCdetails18.Datum)<=##))'.

Bij Access-DB zijn datums toch ingesloten door ## ?
Dank voor een tip.
 
Laatst bewerkt:
Hey,

Ik ben ergens in de beleving gekomen dat je van Acces naar een SQL server ging vanwege de wijzigingen in syntax.

Voor de goede orde.
Je had een volledige Acces database met gebruikersinterface ?
De gebruikers interface is uit Acces naar ASP (webserver) gegaan ?
Je blijft Acces als database gebruiken ?

Als bovenstaande klopt :
Is de view maken gelukt, maw bestaat de view VW_RCdetails18 in de databse ? (Ik twijfel namelijk of Create View werkt in Acces.)

Als de view niet bestaat in Acces :
Query maken en opslaan als VW_RCdetails18 met :
Code:
SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_A18
UNION ALL SELECT Datum, Aantal, Omschrijving, BInVKP, BUitAKP, ledenid FROM tabl_B18
(dit doe je dan gewoon in Acces)

Als de view bestaat of is aangemaakt krijgt de (hoofd) query nog geen datum mee van ASP om te gebruiken.
Code:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(((VW_RCdetails18.Datum)<=[COLOR="#FF0000"]##[/COLOR]))'.

Deze zal uit een variabele uit ASP moeten komen in
Code:
"WHERE (((VW_RCdetails18.Datum)<=#"& [COLOR="#FF0000"][datum?][/COLOR] &"#)) "&_                                     
[B] --Dit is volgens mij een verwijzing naar Acces ?[/B]


"WHERE (((VW_RCdetails18.Datum)<=#"& [COLOR="#006400"]datumvariabeleuitASP [/COLOR]&"#)) "&_                   
[B] --Dit zou het dan moeten zijn.[/B]
"

Ik hoop dat je er een beetje wijs uit wordt.

Frank
 
Voor de duidelijkheid : in eerdere berichten (o.a. eerste en derde en volgende) van deze 'vraag' gaf ik aan wat mijn bedoeling is; dat ik op ASP-server(website) werk met een Access-DB, dezelfde DB als voor mijn standalone Access toepassingen. Alle infovragen hier hebben wel betrekking op de website. Gezien een Access-DB gebruikt wordt zijn er specifieke Access-codevereisten o.a. CASE voor SQL die ook in de website niet geaccepteerd worden.
Bij de conversie van deze SQL query uit Access naar het Web wist ik niet dat een subquery (die in Access standalone bestaat - zie mijn bericht bovenaan) eveneens voor een website kan gecreëerd worden - dank voor tip en aanbreng. En aangezien de query draait in Access standalone moet hij met dezelfde DB ook (kunnen) draaien in Web-versie .
Zover zijn nu - in de Access-DB zie ik inderdaad de view VW_RCdetails18 als een subquery bij de querry's.

Datumcode in hoofdquery is evenwel nog niet OK, geeft nog error.
Als ik het goed begrepen heb, moet de <=#"& datumvariabeleuitASP &"#)) vooraf bepaald zijn in ASP - logisch - had daar even niet aan gedacht - sorry.
Heb dus volgende variabele aangemaakt in ASP (conform de vereisten van een Access-DB o.a. #) :
Einddatum=cdate(#2018/06/30#)
en vervolgens deze variabele in jouw code geplaatst : "WHERE (((VW_RCdetails18.Datum)<="& Einddatum &")) "&_
Geeft evenwel volgende error
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
Vreemd !?
Hoe kan ik dit verder testen / oplossen ?
 
Na wijziging blijft deze error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Dank voor andere mogelijkheden.
 
Kan je in ASP op 1 of andere manier de query echoën en hier plakken. ?
Dus zoals die query naar Acces gaat.

Frank
 
Kan je de variabele SQL op je scherm/bestand/clipboard krijgen ?
(zoals de query naar de DB gaat)

Frank
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan