Bericht 1 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | John Kopmels (Sysop) | Datum | : | 05-10-2004 |
| Aan | : | All | MsgID | : | 1461.1 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hallo,
Ik heb nog 2 lastige query's om te zetten van Access naar SQLserver
het lastige voor mij is het goed opzetten van de derived table, CASE expressies
en vervanger voor de VB Choose functie, ik zou wat hulp zeer op prijs stellen !
WTv en OZv zijn Booleans (dus False of True)
De NZ() functies zijn al vervangen door COALESCE
GVW QUERY :
SELECT tblObjectOnderdelen.Woznr,Vlg,CO,dtIngang,BP,TM,OZv,WTv,Rjr,Bjr,Rperc,
dtPeildatum,Lg,L,B,Opp,H,Inh,E,Perm2,Perm3,PerSt,LeefWrd,RestWrd,FA,FA1,FA2,FA3,
COALESCE([Opp]*[Perm2],0)+COALESCE([Inh]*[Perm3],0)+COALESCE([E]*[PerSt],0) AS P,
(IIf(COALESCE([Rjr],0)>0,0,(IIf(([Bjr]>[dtPeildatum]),1,(IIf((1-((100-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Bjr]))/100)>([RestWrd]/100),(1-((100-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Bjr]))/100),([RestWrd]/100))))))) AS S,
IIf((COALESCE([Rjr],0)>[dtPeildatum]),[Rperc]/100,(IIf([Rjr]>0,(IIf(((100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100)>([RestWrd]/100),(100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100,[RestWrd]/100))))) AS T,
(1-([FA]/100))*(1-([FA1]/100))*(1-([FA2]/100))*(1-([FA3]/100)) AS Y,
IIf(COALESCE([Rperc],0)>0,0,Int([P]*[S]*[Y])) AS Z,
IIf(COALESCE([Rperc],0)>0,Int([P]*[T]*[Y]),0) AS AA,
IIf([WTv],0,Choose([TM],[E]*[PerSt],0,0,[Inh]*[Perm3],[Opp]*[Perm2],[Z]+[AA])) AS Wrd,
Wrd AS WOZwrd, IIf([OZv],0,[Wrd]) AS OZBwrd
FROM tblObject RIGHT JOIN tblObjectOnderdelen ON tblObject.Woznr = tblObjectOnderdelen.Woznr
ORDER BY tblObjectOnderdelen.Woznr,Vlg
WEV QUERY :
SELECT Woznr,Vlg,BP,dtIngang,TM,WTv,OZv,CO,Lg,Bjr,Rjr,Rperc,K,O,ov,L,B,Opp,H,Inh,E,Perm2,Perm3,PerSt,KapFact,
IIf([WTv],0,Choose([TM],[E]*[PerSt],0,([Opp]*[PerM2])*[KapFact],[Inh]*[PerM3],[Opp]*[PerM2],0)) AS Wrd,
Wrd AS WOZwrd,
IIf([OZv],0,[Wrd]) AS OZBwrd
FROM tblObjectOnderdelen
ORDER BY Woznr, Vlg
ik hoop dat er geen rommel staat door evt regelomloop :-)
b.v.b. mijn dank voor de hulp!
Groetjes --John
Bericht 2 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 06-10-2004 |
| Aan | : | John Kopmels (Sysop) | MsgID | : | 1461.2 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi John,
Dat zijn een stel heftige queries, John!! En mede door het overdadige gebruik van (haakjes), [rechte haken] en flnk afgekrt nmn ook lastig te ontcijferen.
Laten we eens kijken.
> WTv en OZv zijn Booleans (dus False of True)
SQL Server kent geen boolean datatype. Veel mensen denken dat datatype BIT equivalent is, maar dat is niet zo. Een Boolean is waar of onwaar (true of false); een BIT is 0, 1 of NULL. Helaas vervangt Microsoft's upsizing wizard een Access Boolean wel door een BIT. Ik geef de voorkeur aan CHAR(1), met een constraint CHECK(Kolomnaam IN ('T','F')) (of, als het voor menselijke uitvoer bestemd is, 'J', 'N' of zelfs 'Ja', 'Nee' (en dan uiteraard als CHAR(3)).
>>
(IIf(COALESCE([Rjr],0)>0,0,(IIf(([Bjr]>[dtPeildatum]),1,(IIf((1-((100-[RestWrd ])/[LeefWrd]*([dtPeildatum]-[Bjr]))/100)>([RestWrd]/100),(1-((100-[RestWrd])/[ LeefWrd]*([dtPeildatum]-[Bjr]))/100),([RestWrd]/100))))))) AS S,
<<
De IIf kan worden omgezet in een CASE. Omdat een CASE meerdere WHEN clauses accepteert en stopt bij de eerste die waar is, kan de geneste structuur zoals die hier gebruikt is (IIf(Test1, Res1, (IIf(Test2, Res2, (IIf(etc))) altijd worden vervangen door n CASE met meerdere WHEN takken. Zie verderop voor wat ik hiervan heb gemaakt.
Ik heb overigens dtPeildatum - Bjr gewoon laten staan, maar als je een aantal dagen van een datum wilt aftrekken kun je hier beter DATEADD voor gebruiken en als je het verschil tussen twee datums wilt hebben is DATEDIFF de betere methode. Op dit moment geeft "datum1 - datum2" een aantal dagen en "datum - getal" resulteert in het genoemde aantal dagen voor "datum", maar dit is niet gedocumenteerd; je loopt het risico dat dit na installatie van een service pack opeens niet meer werkt!
>>
IIf((COALESCE([Rjr],0)>[dtPeildatum]),[Rperc]/100,(IIf([Rjr]>0,(IIf(((100-(([R perc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100)>([RestWr d]/100),(100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc] )))/100,[RestWrd]/100))))) AS T,<<
Deze is wat lastiger, omdat er nu een IIf is genest in het truepart van een andere IIf. Afhankelijk van wat er in het elsepart van die IIf staat kan eventueel alsnog de hiervoor beschreven truk gebruikt worden (door de conditie te inverteren en de true- en elsepart te verwisselen); soms ontkom je er niet aan om in een ELSE ... THEN een extra CASE te nesten.
Nog lastiger wordt deze omdat ik het elsepart van IIf([Rjr]>0,...) niet kan vinden. Het thenpart is zelf een IIf; volgens mij is [RestWrd]/100 het elsepart van die geneste IIf en volgt er geen elsepart meer voor [Rjr]>0. En dat vind ik vreemd, want volgens mij is het elsepart in een IIf niet optioneel. Zie ik iets over het hoofd? Heb jij een foutje gemaakt bij het posten? Ik denk het laatste, want als ik deze formule in een Access module plak, krijg ik ook de foutmelding "argument is niet optioneel". Ik ben bij het omzetten uitgegaan van een extra elsepart "NULL", helemaal aan het eind van de formule. Pas dit zelf aan als het iets anders moet zijn.
Nog enkele opmerkingen:
De eerste test gebruikt COALESCE voor als Rjr niet gevuld is; de tweede test niet. Opzet of vergissing?
Je gebruikt diverse delingen door 100. Ik weet niet wat de datatypes van de diverse kolommen zijn. Zijn dit ook integers, dan zal SQL Server integer deling gebruiken en de rest weggooien. Om niet-integer deling te forceren moet minimaal n van de argumenten geen integer zijn. Vergelijk (via Query Analyzer) maar eens de uitkomsten van SELECT 150/100, 150/100.0
>>
IIf(COALESCE([Rperc],0)>0,0,Int([P]*[S]*[Y])) AS Z,
IIf(COALESCE([Rperc],0)>0,Int([P]*[T]*[Y]),0) AS AA,
<<
Hier gebruik je de aliassen van eerder berekende resultaten in de SELECT. In Access kan dat; SQL Server is daar wat minder soepel in. Je moet dan f een derived table gebruiken, f een view. In mijn voorbeeld gebruik ik een derived table. Heb je liever een view, kopieer dan de gehele geneste SELECT in de FROM clause achter CREATE VIEW xxx AS ...
Overigens kent SQL Server geen functie Int(...). Gebruik in plaats daarvan CAST(... AS int).
>>
[code]IIf([WTv],0,Choose([TM],[E]*[PerSt],0,0,[Inh]*[Perm3],[Opp]*[Perm2],[Z]+[AA]))
AS Wrd,[/code]<<
Hier wordt 'ie helemaal leuk, want nu gebruik je dus weer de velden Z en AA waarvoor hiervoor al een derived table nodig was. De rechttoe rechtaan oplossing is om de derived tables te nesten (ja, dat is toegestane SQL syntax). Maar in dit geval is eenvoudig te zien dat altijd n van de termen Z en AA nul is en de andere gevuld. Ik vervang de term [Z]+[AA] dus door een CASE die hetzij P*S*Y, hetzij P*T*Y oplevert. De termen P en Y kunnen uiteraard ook buiten de CASE gehaald worden (dus: P*Y*CASE ... END).
>>
Wrd AS WOZwrd, IIf([OZv],0,[Wrd]) AS OZBwrd<<
En alsof het allemaal nog niet erg genoeg is, herhaal je nu ook nog eens (twee keer zelfs) de uitkomst van de berekening van Wrd. Geweldig. Omdat ik geen zin heb in nog diepere nesting (in TAPCIS kan de regel maximaal 80 tekens zijn, dan kom je bij een beetje nesting al gauw in de knoei) heb ik de berekening voor Wrd maar gewoon twee keer gekopieerd. Ik kan me voorstellen dat jij hier toch voor een geneste derived table gaat kiezen...
> WEV QUERY :
Is in vergelijking met de GVW query gelukkig een stuk simpeler!
>>
IIf([WTv],0,Choose([TM],[E]*[PerSt],0,([Opp]*[PerM2])*[KapFact],[Inh]*[PerM3], [Opp]*[PerM2],0)) AS Wrd,
<<
Omdat SQL Server geen Boolean datatype kent, zou CASE WHEN Wtv THEN .. een syntactische fout zijn. Dit wordt dus CASE WHEN Wtv = 'T' THEN ... (waarbij je 'T' moet vervangen door de waarde die in jouw systeem "true" aangeeft).
De Choose functie kende ik niet; gelukkig kon de Help-functie van Access mij deze uileggen. Ook dit zal een CASE moeten worden. In dit geval kies ik voor de zogenaamde "simple CASE" (de IIf's heb ik omgezet in "searched CASE"). Ik heb geen ELSE opgenomen - net als bij de Choose heeft ook de CASE een default van NULL als de ELSE-tak ontbreekt.
> ik hoop dat er geen rommel staat door evt regelomloop :-)
Dat hoop ik ook.
Hierna volgen mijn (ongeteste) voorzetjes voor een SQL Server variant op de GVW query en de WEV query. Succes met testen en verder aanpassen en laat het weten als je nog meer hulp nodig hebt!
Groetjes, Hugo
GVW Query:
SELECT Woznr, Vlg, CO, dtIngang, BP, TM, OZv, WTv, Rjr, Bjr, Rperc,
dtPeildatum, Lg, L, B, Opp, H, Inh, E, Perm2, Perm3, PerSt, LeefWrd,
RestWrd, FA, FA1, FA2, FA3, P, S, T, Y,
CASE
WHEN COALESCE (Rperc, 0) > 0
THEN 0
ELSE CAST (P * S * Y AS int)
END AS Z,
CASE
WHEN COALESCE (Rperc, 0) > 0
THEN CAST (P * T * Y AS int)
ELSE 0
END AS AA,
CASE
WHEN Wtv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN Inh * Perm3
WHEN 5 THEN Opp * Perm2
WHEN 6 THEN CASE
WHEN COALESCE (Rperc, 0) > 0
THEN CAST (P * T * Y AS int)
ELSE CAST (P * S * Y AS int)
END
END
END AS Wrd,
CASE
WHEN WTv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN Inh * Perm3
WHEN 5 THEN Opp * Perm2
WHEN 6 THEN CASE
WHEN COALESCE (Rperc, 0) > 0
THEN CAST (P * T * Y AS int)
ELSE CAST (P * S * Y AS int)
END
END
END AS WOZwrd,
CASE
WHEN OZv = 'T' OR WTv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN Inh * Perm3
WHEN 5 THEN Opp * Perm2
WHEN 6 THEN CASE
WHEN COALESCE (Rperc, 0) > 0
THEN CAST (P * T * Y AS int)
ELSE CAST (P * S * Y AS int)
END
END
END AS OZBwrd
FROM (SELECT tblObjectOnderdelen.Woznr, Vlg, CO, dtIngang, BP, TM, OZv,
WTv, Rjr, Bjr, Rperc, dtPeildatum, Lg, L, B, Opp, H, Inh, E,
Perm2, Perm3, PerSt, LeefWrd, RestWrd, FA, FA1, FA2, FA3,
COALESCE (Opp*Perm2, 0) + COALESCE (Inh*Perm3, 0)
+ COALESCE (E*PerSt, 0) AS P,
CASE
WHEN COALESCE (Rjr, 0) > 0
THEN 0
WHEN Bjr > dtPeildatum
THEN 1
WHEN 1 - ((100 - RestWrd)
/ LeefWrd * (dtPeildatum - Bjr)) / 100 > RestWrd / 100
THEN 1 - ((100 - RestWrd)
/ LeefWrd * (dtPeildatum - Bjr)) / 100
ELSE RestWrd / 100
END AS S,
CASE
WHEN COALESCE (Rjr, 0) > dtPeildatum
THEN Rperc / 100
WHEN COALESCE (Rjr, 0) <= 0
THEN NULL
WHEN (100 - ((Rperc - RestWrd)
/ LeefWrd * (dtPeildatum - Rjr) + 100 - Rperc)) / 100
> RestWrd / 100
THEN (100 - ((Rperc - RestWrd)
/ LeefWrd * (dtPeildatum - Rjr) + 100 - Rperc)) / 100
ELSE RestWrd / 100
END AS T,
(1 - (FA / 100)) * (1 - (FA1 / 100))[More]
Bericht 3 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 06-10-2004 |
| Aan | : | Hugo Kornelis | MsgID | : | 1461.3 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
[Continued]
* (1 - (FA2 / 100)) * (1 - (FA3 / 100)) AS Y
FROM tblObject
RIGHT JOIN tblObjectOnderdelen
ON tblObject.Woznr = tblObjectOnderdelen.Woznr) AS Der
ORDER BY Woznr, Vlg
/split
WEV Query:
SELECT Woznr, Vlg, BP, dtIngang, TM, WTv, OZv, CO, Lg, Bjr, Rjr, Rperc,
K, O, ov, L, B, Opp, H, Inh, E, Perm2, Perm3, PerSt, KapFact,
CASE
WHEN WTv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN Opp * PerM2 * KapFact
WHEN 4 THEN Inh * PerM3
WHEN 5 THEN Opp * PerM2
WHEN 6 THEN 0
END
END AS Wrd,
CASE
WHEN WTv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN Opp * PerM2 * KapFact
WHEN 4 THEN Inh * PerM3
WHEN 5 THEN Opp * PerM2
WHEN 6 THEN 0
END
END AS WOZwrd,
CASE
WHEN OZv = 'T' OR WTv = 'T'
THEN 0
ELSE CASE TM
WHEN 1 THEN E * PerSt
WHEN 2 THEN 0
WHEN 3 THEN Opp * PerM2 * KapFact
WHEN 4 THEN Inh * PerM3
WHEN 5 THEN Opp * PerM2
WHEN 6 THEN 0
END
END AS OZBwrd
FROM tblObjectOnderdelen
ORDER BY Woznr, Vlg
Bericht 4 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | John Kopmels (Sysop) | Datum | : | 07-10-2004 |
| Aan | : | Hugo Kornelis | MsgID | : | 1461.4 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi Hugo
Alleerst de opmerking dat ik weer erg blij ben met jou hulp in deze !
Zelf kom ik hier zeker niet uit...
> Dat zijn een stel heftige queriesmede door het overdadige
gebruik van (haakjes) [rechte haken] en flink afgekort
Je snapt het misschien al, eerste versie had ik ooit in Excel gemaakt
en is daarna regelmatig gewijzigd, toen over naar Access dat was nog
goed te doen voor mij , en nu weer naar SQL server en voor dit soort
queries moet je toch al redelijk wat ervaring in SQL server hebben..
>> WTv en OZv zijn Booleans (dus False of True)
> SQL Server kent geen boolean datatype.
Veel mensen denken dat datatype BIT equivalent is, maar dat is niet zo.
Een Boolean is waar of onwaar (true of false); een BIT is 0, 1 of NULL.
Ik geef de voorkeur aan CHAR(1), met een constraint CHECK(Kolomnaam IN ('T','F'))
Goeie tip, dat ga ik ook doen wordt bij ons J of N
>> IIf(COALESCE([Rjr],0)>0,0,(IIf(([Bjr]>[dtPeildatum]),1,(IIf((1-((100-[RestWrd ])/[LeefWrd]*([dtPeildatum]-
[Bjr]))/100)>([RestWrd]/100),(1-((100-[RestWrd])/[ LeefWrd]*([dtPeildatum]-[Bjr]))/100),([RestWrd]/100)))))) AS S,
> De IIf kan worden omgezet in een CASE.
Omdat een CASE meerdere WHEN clauses accepteert en stopt bij de eerste die waar is,
kan de geneste structuur zoals die hier gebruikt is (IIf(Test1, Res1, (IIf(Test2, Res2, (IIf(etc)))
altijd worden vervangen door n CASE met meerdere WHEN takken. Zie verderop voor wat ik hiervan heb gemaakt.
OK hebbes
> Ik heb overigens dtPeildatum - Bjr gewoon laten staan,
maar als je een aantal dagen van een datum wilt aftrekken kun je hier beter DATEADD voor gebruiken
en als je het verschil tussen twee datums wilt hebben is DATEDIFF de betere methode.
De datum issues heb ik al regelmatig in de groepen langs zien komen, de functies verschillen
vrijwel niet van die in Access dus das geen probleem, enigste verschil is vaak julie conversies
Hier doet dit niet ter zaken omdat het beide Integers zijn: dtPeildatum b.v. 2003 en Bjr 1970
>> IIf(COALESCE([Rjr],0)>[dtPeildatum],
[Rperc]/100,
(IIf([Rjr]>0,(IIf(((100-(([R perc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100)>([RestWr d]/100),
(100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc] )))/100,[RestWrd]/100))))) AS T,
> Deze is wat lastiger, omdat er nu een IIf is genest in het truepart van een andere IIf.
Afhankelijk van wat er in het elsepart van die IIf staat kan eventueel alsnog de hiervoor
beschreven truk gebruikt worden (door de conditie te inverteren en de true- en elsepart te verwisselen)
soms ontkom je er niet aan om in een ELSE ... THEN een extra CASE te nesten.
Nog lastiger wordt deze omdat ik het elsepart van IIf([Rjr]>0,...) niet kan vinden.
Het thenpart is zelf een IIf; volgens mij is [RestWrd]/100 het elsepart van die geneste IIf
en volgt er geen elsepart meer voor [Rjr]>0.
En dat vind ik vreemd, want volgens mij is het elsepart in een IIf niet optioneel.
Zie ik iets over het hoofd? Heb jij een foutje gemaakt bij het posten?
Ik ben bij het omzetten uitgegaan van een extra elsepart "NULL",
helemaal aan het eind van de formule. Pas dit zelf aan als het iets anders moet zijn.
Toch niet lijkt me, hier de orginele (gedeelte uit orgineel apart getest) die bij mij werkt
SELECT tblObjectOnderdelen.Woznr,
' voorwaarde:
IIf(NZ([Rjr],0)>[dtPeildatum],
' truepart
[Rperc]/100,
' falsepart (tot einde)
(IIf([Rjr]>0,
' voorwaarde (genest)
IIf(((100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100)>([RestWrd]/100),
' truepart (genest)
(100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])+(100-[Rperc])))/100,
' falsepart (genest)
[RestWrd]/100)))) AS T
FROM tblObjectOnderdelen INNER JOIN tblObject ON tblObjectOnderdelen.Woznr = tblObject.Woznr;> Nog enkele opmerkingen:
De eerste test gebruikt COALESCE voor als Rjr niet gevuld is; de tweede test niet. Opzet of vergissing?
vergissing :-(
> Je gebruikt diverse delingen door 100. Ik weet niet wat de datatypes van de diverse kolommen zijn.
Zijn dit ook integers, dan zal SQL Server integer deling gebruiken en de rest weggooien.
Om niet-integer deling te forceren moet minimaal n van de argumenten geen integer zijn.
Vergelijk (via Query Analyzer) maar eens de uitkomsten van SELECT 150/100, 150/100.0
OK
>> IIf(COALESCE([Rperc],0)>0,0,Int([P]*
*[Y])) AS Z,
>> IIf(COALESCE([Rperc],0)>0,Int([P]*[T]*[Y]),0) AS AA,
Hier gebruik je de aliassen van eerder berekende resultaten in de SELECT.
In Access kan dat; SQL Server is daar wat minder soepel in.
Je moet dan f een derived table gebruiken, f een view.
Had je al eens gemeld, zie ook mijn orginele vraag, ik weet van jou
dat dit zo moet maar kwam er (zeker in deze context niet uit)
> Overigens kent SQL Server geen functie Int(...). Gebruik in plaats daarvan CAST(... AS int).
Dat doe ik al ja en is bekend
>>IIf([WTv],0,Choose([TM],[E]*[PerSt],0,0,[Inh]*[Perm3],[Opp]*[Perm2],[Z]+[AA])) AS Wrd,
> Hier wordt 'ie helemaal leuk, want nu gebruik je dus weer de velden Z en AA waarvoor
hiervoor al een derived table nodig was. De rechttoe rechtaan oplossing is om de
derived tables te nesten (ja, dat is toegestane SQL syntax). Maar in dit geval is
eenvoudig te zien dat altijd n van de termen Z en AA nul is en de andere gevuld.
Ik vervang de term [Z]+[AA] dus door een CASE die hetzij P*S*Y, hetzij P*T*Y oplevert.
De termen P en Y kunnen uiteraard ook buiten de CASE gehaald worden (dus: P*Y*CASE ... END).
is voor mij (momenteel) nog te lastig om te bedenken
>> Wrd AS WOZwrd, IIf([OZv],0,[Wrd]) AS OZBwrd
> En alsof het allemaal nog niet erg genoeg is, herhaal je nu ook nog eens (2 keer zelfs)
de uitkomst van de berekening van Wrd. Geweldig. Omdat ik geen zin heb in nog diepere
nesting heb ik de berekening voor Wrd maar gewoon twee keer gekopieerd.
Ik kan me voorstellen dat jij hier toch voor een geneste derived table gaat kiezen...
>> WEV QUERY :
> Is in vergelijking met de GVW query gelukkig een stuk simpeler!
dat dacht ik ook , zie het maar als een soort toetje :-)
>> IIf([WTv],0,Choose([TM],[E]*[PerSt],0,([Opp]*[PerM2])*[KapFact],[Inh]*[PerM3], [Opp]*[PerM2],0)) AS Wrd,
> Omdat SQL Server geen Boolean datatype kent, zou CASE WHEN Wtv THEN .. een syntactische fout zijn.
Dit wordt dus CASE WHEN Wtv = 'T' THEN ...
(waarbij je 'T' moet vervangen door de waarde die in jouw systeem "true" aangeeft).
De Choose functie kende ik niet; gelukkig kon de Help-functie van Access mij deze uileggen.
Ook dit zal een CASE moeten worden. In dit geval kies ik voor de zogenaamde "simple CASE"
(de IIf's heb ik omgezet in "searched CASE"). Ik heb geen ELSE opgenomen - net als bij de Choose
heeft ook de CASE een default van NULL als de ELSE-tak ontbreekt.
> Hierna volgen mijn (ongeteste) voorzetjes voor een SQL Server variant op de GVW query en de WEV query.
Succes met testen en verder aanpassen en laat het weten als je nog meer hulp nodig hebt!
De boel moet nu nog om naar de orginele STUF TAX tabellen (tblObject en tblOnderdelen
waren eigen tabellen) en ik zal nog wel enkele eigenaardigheden tegenkomen, ik ga de boel
nu eerst uittesten en zien te begrijpen en kom dan ongetwijfeld nog wel terug
Nogmaals mijn dank! en groetjes --John
Bericht 5 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | John Kopmels (Sysop) | Datum | : | 07-10-2004 |
| Aan | : | Hugo Kornelis | MsgID | : | 1461.5 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi Hugo,
Ik heb ze inmiddels wat gestest, aangepast en omgezet naar de
nieuwe tabelstruktuur, het lijkt erop dat alles helemaal goed
gaat, alleen nog in de forms inbouwen en wat uitkomsten testen.
-------------------------------------------------------------
Tijdens het omzetten liep ik nog tegen een probleempje
aan waar ik jou nog even voor de zekerheid wil raadplegen :
In een ADP is het resultaat van een Stored Procedure die
bestaat uit meerdere gejoinde tabellen niet editable, is
dit ook zo in SQL server zelf dus met een ander frontend ?
In de lastige SP zat een INNER JOIN en zodra ik die opzet
is de SP dus Read-Only (pk's zijn ok) in een VIEW kan ik
in dezelfde situatie wel editen
In een form gelden andere regels als je daar de Unique Table
Property instelt is ie (meestal) weer wel te editen
Wat zijn zo'n beetje de regels en wat is 'normaal'
9afgezien van Access/ADP als frontend)
Hierop verder gaand : dtPeildatum die gebruikt werd kwam uit
een andere tabel (tblObject) en is naast Wtv het enige veld
wat ik nog nodig heb uit die tabel, om een join te voorkomen
zou ik Wtv in de nieuwe tabel kunnen stoppen, dtPeildatum is
al een variabele die ik ophaal, dus alleen Wtv heb ik nog nodig
Nu kom ik er nog wel omheen maar dit zal ongetwijfeld vaker
voor gaan komen, hoe los jij dat meestal op ?
Is er een andere oplossing, b.v. met subqueries ?
Groetjes --John
Bericht 6 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 07-10-2004 |
| Aan | : | John Kopmels (Sysop) | MsgID | : | 1461.6 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi John,
> De datum issues heb ik al regelmatig in de groepen langs zien komen, de
> functies verschillen
> vrijwel niet van die in Access dus das geen probleem, enigste verschil is
> vaak julie conversies
Probeer conversies van datum naar char/varchar in SQL Server te voorkomen als het even gaat. Opmaak van de datum kun je beter regelen in de presentatie layer. Dan kun je bijvoorbeeld ook gebruik maken van degionale instellingen van de client computer.
Als je toch datums moet gaan opmaken in SQL Server, gebruik dan CONVERT i.p.v. CAST en lees in Books Online welke formaten je hiermee kunt forceren.
> > Nog lastiger wordt deze omdat ik het elsepart van IIf([Rjr]>0,...)
> > niet kan vinden.
(...)
> > Zie ik iets over het hoofd? Heb jij een foutje gemaakt bij het posten?
(...)
> Toch niet lijkt me, hier de orginele (gedeelte uit orgineel apart getest)
> die bij mij werkt
>
> SELECT tblObjectOnderdelen.Woznr,
>
> ' voorwaarde:
> IIf(NZ([Rjr],0)>[dtPeildatum],
> ' truepart
> [Rperc]/100,
> ' falsepart (tot einde)
> (IIf([Rjr]>0,
> ' voorwaarde (genest)
> IIf(((100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])
> +(100-[Rperc])))/100)>([RestWrd]/100),
> ' truepart (genest)
> (100-(([Rperc]-[RestWrd])/[LeefWrd]*([dtPeildatum]-[Rjr])
> +(100-[Rperc])))/100,
>
> ' falsepart (genest)
> [RestWrd]/100)))) AS T
>
> FROM tblObjectOnderdelen INNER JOIN tblObject
> ON tblObjectOnderdelen.Woznr = tblObject.Woznr;
Ook de bovenstaande versie mist een falsepart. Even schematisch, misschien zie jij dan wat ik over het hoofd zie (gebruik een fixed font; ik vrees dat in web view sowieso de spaties zullen worden opgegeten, dus ik hoop dat je niet via webview werkt...)
' voorwaarde (nesting nivo 1)
+--------> IIf(NZ([RJr],0)>[dtPeildatum],
| ' truepart (nesting nivo 1)
+--------> [RPerc]/100,
| ' falsepart (nesting nivo 1)
| ' ook voorwaarde (nesting nivo 2)
+--+-----> (IIf([RJr]>0,
| ' truepart (nesting nivo 2)
| ' ook voorwaarde (nesting nivo 3)
+--+--> IIf((ingewikkelde formule)>([RestWrd]/100),
| | ' truepart (nesting nivo 3)
| +--> ingewikkelde formule,
| | ' falsepart (nesting nivo 3)
| +--> [RestWrd]/100)))) AS T
|
+-----> En hier ontbreekt dus het elsepart van nesting nivo 2!!!Als je me kunt uitleggen wat ik verkeerd begrijp in jouw query, dan kan ik daarna kijken wat er moet veranderen in mijn voorzetje voor de query om aan te sluiten bij wat je wl bedoelde.
> De boel moet nu nog om naar de orginele STUF TAX tabellen (tblObject en
> tblOnderdelen
> waren eigen tabellen) en ik zal nog wel enkele eigenaardigheden
> tegenkomen, ik ga de boel
> nu eerst uittesten en zien te begrijpen en kom dan ongetwijfeld nog wel
> terug
Ok. Succes met de omzetting - en als je vragen hebt hoor ik het!
Groetjes, Hugo
Bericht 7 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 07-10-2004 |
| Aan | : | John Kopmels (Sysop) | MsgID | : | 1461.7 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi John,
> In een ADP is het resultaat van een Stored Procedure die
> bestaat uit meerdere gejoinde tabellen niet editable, is
> dit ook zo in SQL server zelf dus met een ander frontend ?
Ik neem aan (en hoop!) dat het resultaat van een SP die uit slechts n tabel bestaat evenmin gewijzigd kan worden.
Een SP kan een hele reeks opdrachten bevatten. Daar kunnen inserts, updates en deletes tussen zitten. Er kunnen ook een of zelfs meer resultsets uit een SP komen. Maar gezien de enorme variatiemogelijkheden is een wijziging van gegevens in die resultset dus onmogelijk terug te vertalen naar de gegevens waar die resultset op gebaseerd is.
> In de lastige SP zat een INNER JOIN en zodra ik die opzet
> is de SP dus Read-Only (pk's zijn ok) in een VIEW kan ik
> in dezelfde situatie wel editen
Als je met Profiler een trace draait, dan kun je zien welke SQL opdrachten door ADP naar SQL Server worden gestuurd als jij de output van een join edit. Ik verwacht dat je een statement tegen zult komen zoals
UPDATE MijnView
SET GewijzigdeKolom = 'NieuweGegevens'
WHERE SleutelKolom = 'SleutelGegevens'Een view in SQL Server kan, onder bepaalde voorwaarden, gemuteerd worden. Net als een query in Access. Die voorwaarden kunnen heel kort samengevat worden als "SQL Server moet in staat zijn de wijziging te vertalen naar een equivalente wijziging op de onderliggende tabellen".
Een stored procedure kan in SQL Server niet gemuteerd worden (evenmin als een module in Access). De syntax UPDATE procedurenaam ... is niet toegestaan. En wat SQL Server niet toestaat kan ADP je ook niet bieden.
> In een form gelden andere regels als je daar de Unique Table
> Property instelt is ie (meestal) weer wel te editen
Bedoel je dat je de uitvoer van een stored procedure in een form haalt en die dan kan editten? Als dat zo is, dan ben ik nu wel hl benieuwd welke commando's ADP hiervoor naar de server stuurt.
> Wat zijn zo'n beetje de regels en wat is 'normaal'
> 9afgezien van Access/ADP als frontend)
De regels voor updatable views in SQL Server kun je vinden in Books Online; zoek in de index naar trefwoord CREATE VIEW, kies de beschrijving uit de Transact-SQL Reference en blader door naar beneden tot je het kopje over Updatable views tegenkomt.
> Hierop verder gaand : dtPeildatum die gebruikt werd kwam uit
> een andere tabel (tblObject) en is naast Wtv het enige veld
> wat ik nog nodig heb uit die tabel, om een join te voorkomen
> zou ik Wtv in de nieuwe tabel kunnen stoppen, dtPeildatum is
> al een variabele die ik ophaal, dus alleen Wtv heb ik nog nodig
Waarom zou je het willen oplossen? In een genormaliseerde database komen joins voor. Wat is daar mis mee?
> Nu kom ik er nog wel omheen maar dit zal ongetwijfeld vaker
> voor gaan komen, hoe los jij dat meestal op ?
Om te beginnen doe ik maar heel weinig aan frontend ontwikkeling, en wat ik doe is erg simpel. Misschien is dat de reden dat ik je probleem niet begrijp?
Als je de gebruiker de mogelijkheid wilt geven om de gegevens op het scherm te wijzigen, dan kan dat volgens mij ruwweg op de volgende manieren:
1. Maak een view in plaats van een stored procedure en baseer daar je scherm op. Als de view voldoet aan de criteria voor updatable view is dat voldoende.
2. Maak een apart scherm voor bekijken van de gegevens (met allerlei leuke extra, gerelateerde gegevens uit andere tabellen erbij) en een apart scherm voor muteren (met alleen de gegevens uit n tabel).
3. Vang de wijzigingen die de gebruiker uitvoert op in de diverse events, vertaal ze zelf in de corresponderende mutaties voor de basis tabellen en stuur die mutaties als SQL-string naar de server (of maak stored procedures voor die wijzigingen op de server en roep de betreffende proc met de juiste parameters aan vanuit de gebeurtenisprocedures).
Wellicht zijn er nog andere opties, maar die moet je dan in het front end vinden en maken. SQL Server staat uitsluitend updates toe op tabellen en op views die aan de criteria voor updatable views voldoen.
> Is er een andere oplossing, b.v. met subqueries ?
In de stored procedure zal je dit niet helpen - een stored procedure is nooit updatable. In de view is het niet nodig, want die is nu al updatable en hoeft dus niet te worden gewijzigd.
Groetjes, Hugo
Bericht 8 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | John Kopmels (Sysop) | Datum | : | 08-10-2004 |
| Aan | : | Hugo Kornelis | MsgID | : | 1461.8 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi Hugo
> Ik neem aan (en hoop!) dat het resultaat van een SP die
> uit slechts n tabel bestaat evenmin gewijzigd kan worden.
Toch wel, net even als test gedaan (adp eigen, zie verder)
: The reason the stored procedure is updateable from the Access user interface
: is because Access figures out the underlying data source of the stored procedure
: and updates the data source directly, bypassing the stored procedure.
> Een SP kan een hele reeks opdrachten bevatten.
> Daar kunnen inserts, updates en deletes tussen zitten.
> Er kunnen ook een of zelfs meer resultsets uit een SP komen.
> Maar gezien de enorme variatiemogelijkheden is een wijziging
> van gegevens in die resultset dus onmogelijk terug te vertalen
> naar de gegevens waar die resultset op gebaseerd is.
OK das duidelijk, uiteindelijk wil ik ook niet editen *binnen* de sp
maar wel binnen het recordset wat het produceerd, en om te kijken of
het updateable is kijk ik dan rechtstreeks in de sp zelf (onnodig dus)
>> In een form gelden andere regels als je daar de Unique Table
>> Property instelt is ie (meestal) weer wel te editen
> Bedoel je dat je de uitvoer van een stored procedure in een form
> haalt en die dan kan editten? Als dat zo is, dan ben ik nu hl
> benieuwd welke commando's ADP hiervoor naar de server stuurt.
in een adp kan je simpelweg een sp als recordsource van een form of
rapport nemen en in dat form en rapport de data bewerken, de sp zelf
wordt niet bewerkt maar het is de UI die de edits en inserts verstuurd
>> Hierop verder gaand : dtPeildatum die gebruikt werd kwam uit
>> een andere tabel (tblObject) en is naast Wtv het enige veld
>> wat ik nog nodig heb uit die tabel, om een join te voorkomen
>> zou ik Wtv in de nieuwe tabel kunnen stoppen, dtPeildatum is
>> al een variabele die ik ophaal, dus alleen Wtv heb ik nog nodig
> Waarom zou je het willen oplossen? In een genormaliseerde database
> komen joins voor. Wat is daar mis mee?
t'uurlijk is daar niets mis mee, integendeel, maar ik wil wel kunnen
werken in de forms, dus dacht ik vooraf te controleren of het resultaat
wel updateable is, das dus achteraf en bij nader inzien onzin dat te doen
omdat een adp je toch voor de gek houd, in een form is het net weer anders
het rottige is dat je jezelf wezeloos zoekt hoe dat zo'n beetje in elkaar
steekt en dit verhaal nergens gedokumenteerd is, ik heb het gelezen in een
bericht van n van de programmeurs van MS (na uren zoeken) je kan je dus
wel voorstellen dat het verwarrend is als je overal leest dat een sp niet
updatable en je dat in een adp wel constant kan doen en er bijna niets over
te vinden is, overigens is het nu inmiddels wel redelijk duidelijk
de meeste boeken wijden 1 karig lullig nietszeggend hoofdstukje aan een adp
er is een verouderd boek van Andy Baron/Mary Chipman en een wat recenter boek
van Rolf Albrecht/Natascha Nicol, thats all, maar niets over dit onderwerp
> Als je de gebruiker de mogelijkheid wilt geven om de gegevens op het
> scherm te wijzigen, dan kan dat volgens mij ruwweg op de volgende manieren:
> (knip)
In een adp is dat dus allemaal anders, en wellicht makkelijker, alleen is
het vaak verwarrend als je sql server berichten leest en daarnaast in een
adp werkt die vol zit met eigenaardigheden, bovendien moet ik nog rekening
houden met m'n collega die het webbased bouwt, daar moet ook alles werken
In elk geval heeft jou antwoord me bewogen wat tijd in verder zoeken
te steken en is eea nu aardig duidelijk, bedankt voor het meedenken !
Groetjes --John
Bericht 9 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | John Kopmels (Sysop) | Datum | : | 08-10-2004 |
| Aan | : | Hugo Kornelis | MsgID | : | 1461.9 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi Hugo,
ik gebruik een fixed font en ik werk met Ozwin
' voorwaarde (nesting nivo 1)
+--------> IIf(NZ([RJr],0)>[dtPeildatum],
| ' truepart (nesting nivo 1)
+--------> [RPerc]/100,
| ' falsepart (nesting nivo 1)
| ' ook voorwaarde (nesting nivo 2)
+--+-----> (IIf([RJr]>0,
| ' truepart (nesting nivo 2)
| ' ook voorwaarde (nesting nivo 3)
+--+--> IIf((ingewikkelde formule)>([RestWrd]/100),
| | ' truepart (nesting nivo 3)
| +--> ingewikkelde formule,
| | ' falsepart (nesting nivo 3)
| +--> [RestWrd]/100)))) AS T
|
+-----> En hier ontbreekt dus het elsepart van nesting nivo 2!!!good catch je hebt gelijk ! er moest nog een nul staan, zie hieronder
SELECT tblObjectOnderdelen.Woznr,
IIf((nz([Rjr],0)>2003),
[Rperc]/100,
(IIf([Rjr]>0,
(IIf(((100-(([Rperc]-[RestWrd])/[LeefWrd]*(2003-[Rjr])+(100-[Rperc])))/100)>([RestWrd]/100),
(100-(([Rperc]-[RestWrd])/[LeefWrd]*(2003-[Rjr])+(100-[Rperc])))/100,[RestWrd]/100)),0))) AS T>> De boel moet nu nog om naar de orginele STUF TAX tabellen
> Ok. Succes met de omzetting - en als je vragen hebt hoor ik het!
het is gebeurt en beide werken, er zit allen wat afwijking in de getallen
door andere afronding en haak die waarschijnlijk ergens verkeerd stond ?
dat laatste zoek ik nog uit, het is nu veel leesbaarder geworden dus ook
makkelijker te onderhouden, je hebt me 'wreed goe' geholpen !
ps het form is updateable :-) zie message #191075
Groetjes --John
Bericht 10 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 09-10-2004 |
| Aan | : | John Kopmels (Sysop) | MsgID | : | 1461.10 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi John,
> good catch je hebt gelijk ! er moest nog een nul staan, zie hieronder
Aha. Op die plek had ik bij mijn omzetting als aanname een NULL ingevuld. Ik ben blij dat de missende informatie niet ergens anders moest worden tussengevoegd, want dan had ik mijn CASE expressie weer helemaal opnieuw moeten besturderen. Nu kun je volstaan met daar waar nu (in mijn SQL suggestie) "THEN NULL" staat, dit te vervangen door "THEN 0". (Dat is het getal nul, niet de letter oo).
> het is gebeurt en beide werken, er zit allen wat afwijking in de getallen
> door andere afronding en haak die waarschijnlijk ergens verkeerd stond ?
Oei.... Ja, het kn inderdaad kloppen dat ik in mijn ijver om te kappen in het woud van open- en sluithaken iets te fanatiek ben geweest. Ik denk van niet, maar ik durf het niet 100% uit te sluiten. Dat kunnen wel hele lastige fouten zijn om op te sporen.
> je hebt me 'wreed goe' geholpen !
Graag gedaan! :-)
Groetjes, Hugo
Bericht 11 van 11NL Computer Forum ~ SQL & Programmeren | Van | : | Hugo Kornelis | Datum | : | 09-10-2004 |
| Aan | : | John Kopmels (Sysop) | MsgID | : | 1461.11 |
| Onderwerp | : | Access = SQLserver | Forum | : | ws-nlcomputer |
Hoi John,
> : The reason the stored procedure is updateable from the Access user
> interface
> : is because Access figures out the underlying data source of the stored
> procedure
> : and updates the data source directly, bypassing the stored procedure.
Ugh. Yech. Blah!
Ik heb toch zo'n hekel aan tools die proberen voor je te denken. Vooral omdat ze het some wel eens bij het verkeerde eind hebben - en probeer ze dn maar eens van het tegendeel te overtuigen.....
> het rottige is dat je jezelf wezeloos zoekt hoe dat zo'n beetje in elkaar
> steekt en dit verhaal nergens gedokumenteerd is, ik heb het gelezen in een
> bericht van n van de programmeurs van MS (na uren zoeken) je kan je dus
> wel voorstellen dat het verwarrend is als je overal leest dat een sp niet
> updatable en je dat in een adp wel constant kan doen en er bijna niets over
> te vinden is
(...)
Yep. Zulke wezenlijke functionaliteit hoort duidelijk en expliciet gedocumenteerd te zijn (liefst met een grote rode sticker: NIET AANKOMEN).
Ik ben bij dat je deze informatie hebt kunnen vinden en hier hebt neergezet. Ik wist dit niet - en het is toch behoorlijk essentiele informatie!!
> > Als je de gebruiker de mogelijkheid wilt geven om de gegevens op het
> > scherm te wijzigen, dan kan dat volgens mij ruwweg op de volgende
> manieren:
> > (knip)
>
> In een adp is dat dus allemaal anders, en wellicht makkelijker, alleen is
> het vaak verwarrend als je sql server berichten leest en daarnaast in een
> adp werkt die vol zit met eigenaardigheden, bovendien moet ik nog rekening
> houden met m'n collega die het webbased bouwt, daar moet ook alles werken
De doorgaans aanbevolen methode (o.a. omdat die het best beveiligd is) is het gebruik van speciale stored procedures voor alle wijzigingen. Je hoeft dan niemand wijzigingsrechten op de tabellen te geven; een uitvoerpermissie voor de betreffende procedure(s) volstaat.
Dit levert uiteraard wel extra werk op bij de ontwikkeling van het frontend. Als dat binnen jouw project niet mogelijk is, dan adviseer ik je met name te kijken naar het gebruik van views die voldoen aan SQL Server's normen voor updatable views. Dan kun je er in elk geval op vertrouwen dat Access de wijzigingsopdrachten rechttoe-rechtaan afleidt van de gebruikersacties en niet op basis van de SP-code gaat proberen zelf een fancy vertaling in elkaar te zetten.
Groetjes, Hugo
