Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

192 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

29 maart 2024, 03:34:37

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: SQLserver procedure  (gelezen 12730 keer)

0 leden en 1 gast bekijken dit topic.

Offline NLCOMP

  • Forumheld
  • *****
  • Berichten: 14.666
    • NL Computer Forum
SQLserver procedure
« Gepost op: 8 november 2009, 23:01:22 »
Bericht 1 van 4

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:07-08-2004
 Aan:AllMsgID:1455.1
 Onderwerp:SQLserver procedureForum:ws-nlcomputer
Hallo,

In een uitgebreide berekening zit een stukje dynamische
sql wat de grondprijs 'gestaffeld' uit moet rekenen

het volgende werkt al lange tijd goed in Access, maar
moet omgezet worden naar iets wat SQLserver 'begrijpt'

De tblStaffel heeft onderstaande velden waarbij
Buurt,Type,Tijdvak de unieke primairy key vormen

'P' kolommen Prijzen zijn (bv P1:400 P2:150 P3:100 P4:50 >4:25)
'K' kolommen Knikpunten zijn (bv K1:200 K2:400 K3:600 K4:800)
'Kavel' de uitkomst van de berekening is

hoe pak ik dit het best aan ...?

S = "SELECT Buurt,Type,Tijdvak,Opp,P1,K1,P2,K2,P3,K3,P4,K4,[>4], " & _
"IIf([Opp]>=[K1],[K1],[Opp]) AS M, " & _
"IIf([Opp]>=[K2],([K2]-[K1]), " & _
"IIf([Opp]-[K1]>0,[Opp]-[K1],0)) AS N, " & _
"IIf([Opp]>=[K3],[K3]-[K2], " & _
"IIf([Opp]-[K2]>0,[Opp]-[K2],0)) AS O, " & _
"IIf([Opp]>=[K4],[K4]-[K3], " & _
"IIf([Opp]-[K3]>0,[Opp]-[K3],0)) AS P, " & _
"IIf([Opp]>[K4],[Opp]-[K4],0) AS Q, " & _
"([M]*[P1])+([N]*[P2])+([O]*[P3])+([P]*[P4])+([Q]*[>4]) AS Kavel " & _
"FROM tblStaffel " & _
"WHERE Buurt=" & Chr(39) & sWB & Chr(39) & _
" AND Type=" & Chr(39) & sType & Chr(39)

voor de volledigheid de DDL van tblStaffel :

CREATE TABLE tblStaffel (
Buurt VARCHAR(2) NOT NULL,
Type VARCHAR(1) NOT NULL,
Tijdvak VARCHAR(10) NOT NULL,
Opp LONG,
P1 LONG,
K1 LONG,
P2 LONG,
K2 LONG,
P3 LONG,
K3 LONG,
P4 LONG,
K4 LONG,
>4 LONG,
Kavelwaarde LONG,
CONSTRAINT PrimaryKey PRIMARY KEY (Buurt, Type, Tijdvak)
)

b.v.b. mijn dank

groetjes --John



Bericht 2 van 4

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:07-08-2004
 Aan:John Kopmels (Sysop)MsgID:1455.2
 Onderwerp:SQLserver procedureForum:ws-nlcomputer
Hoi John,

Volgens de ANSI SQL-92 standaard, maar ook volgens de recentere (niet door ANSI overgenomen) SQL-99 en SQL-2003 wordt de gehele SELECT clause ineens gevalueerd; daardoor is het niet mogelijk om binnen n SELECT clause een expressie een alias te geven en die alias elders in dezelfde clause weer te gebruiken. Access heeft zich nooit aan dit deel van de standaard gehouden, wat in veel gevallen (zoals de SQL die jij nu probeert te converteren) erg gemakkelijk is, maar ook gevaarlijk. Probeer maar eens te voorspellen wat de output van de volgende query is als je toestaat om aliassen binnen de SELECT clause te gebruiken:

SELECT A, B as A, A - B as Verschil
FROM (SELECT 3 as A, 5 as B
UNION
SELECT 17 as A, 20 as B) as TestTabel
WHERE A > 4

(Om dit in Access te gebruiken moet je eerst een tabel TestTabel maken met kolommen A en B en de rijen (3, 5) en (17, 20) erin.)

Hoewel er dus goede redenen zijn waarom de ANSI/SQL standaards zijn zoals ze zijn en waarom SQL Server wl volgens die standaards werkt, betekent dit voor queries zoals die van jou extra werk. Met name bij oudere versies van SQL Server was er geen ander alternatief dan hetzij de expressie te herhalen, hetzij de tussenresultaten in een tijdelijke tabel te stoppen. Gelukkig is er sinds (als ik het me gode herinner) versie 6.5 van SQL Server support voor derived tables - zeg maar het opnemen van een tijdelijke tabel in de FROM clause. Logisch gezien wordt eerst een tijdelijke tabel gemaakt met het resultaat van de subquery in de FROM-clause, waarna die in de uiteindelijke query wordt gebruikt. In de praktijk mag de optimizer kiezen voor een andere aanpak, mits het resultaat gelijk blijft. De simpele query hierboven bevat al een voorbeeld van een derived table.

Voor jouw query betekent dit dat we de IIf constructies moeten vervangen door equivalente CASE constructies, het geheel als derived table in een andere query moeten opnemen en dan kunnen we in die andere query wl verwijzen naar de aliassen (want dat zijn de kolomnamen van de derived table geworden). We krijgen dan dus zoiets als dit:

SELECT Buurt, Type, Tijdvak, Opp,
P1, K1, P2, K2, P3, K3, P4, K4, [>1],
M, N, O, P, Q,
(M*P1 + N*P2 + O*P3 + P*P4 + Q*[>4]) AS Kavel
FROM (SELECT Buurt, Type, Tijdvak, Opp,
P1, K1, P2, K2, P3, K3, P4, K4, [>1],
CASE
WHEN Opp >= K1
THEN K1
ELSE Opp
END AS M,
CASE
WHEN Opp >= K2
THEN K2 - K1
WHEN Opp > K1
THEN Opp - K1
ELSE 0
END AS N,
CASE
WHEN Opp >= K3
THEN K3 - K2
WHEN Opp > K2
THEN Opp - K2
ELSE 0
END AS O,
CASE
WHEN Opp >= K4
THEN K4 - K3
WHEN Opp > K3
THEN Opp - K3
ELSE 0
END AS P,
CASE
WHEN Opp > K4
THEN Opp - K4
ELSE 0
END AS Q
FROM Staffel
WHERE Buurt = 'AB'
AND Type = 'C') AS X -- Alias is verplicht bij derived table!
(ongetest)

Overigens neem ik aan dat ik je niet hoef te vertellen dat dit ontwerp niet genormaliseerd is, dat kolomnamen beter een betekenisvolle naam kunnen krijgen en dat namen zoals >4 sowieso onhandig zijn omdat er gereserveerde tekens in voorkomen.

Verder is LONG geen datatype dat SQL Server kent. Bedoel je soms BIGINT? En heb je echt zulke grote getallen nodig? Met INT kom je al tot ruim 2 miljard; ik zou niet graag tuinman zijn in een kavel waar BIGINT echt voor nodig is. Ik denk dat je zelfs met SMALLINT (max 32767) al een heel eind komt, maar INT is zodanig standaard dat je je daar geen buil aan kunt vallen.

Tenslotte: voor Buurt en Type zou ik CHAR nemen in plaats van VARCHAR. Voor elke VARCHAR waarde is behalve het aantal bytes van de echte inhoud ook nog 2 bytes extra ruimte nodig om de lengte vast te leggen; je hebt dus alleen voordeel van VARCHAR t.o.v. CHAR als de maximale lengte groot is en een aanzienlijk deel van de waarden niet de gehele lengte gebruikt. Bij CHAR(1) en CHAR(2) kan dat nooit. Bij CHAR(10) (tijdvak) wel, maar dat hangt af van welke waarden daar normaal in staan - als alle waarden 8, 9 of 10 tekens zijn zou ik ook hier voor CHAR kiezen i.p.v. VARCHAR.

Ik hoop dat ik tussen alle wijze lessen door niet vergeten ben om ook je eigenlijk vraag te beantwoorden :-P

Groetjes, Hugo


Bericht 3 van 4

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:09-08-2004
 Aan:Hugo KornelisMsgID:1455.3
 Onderwerp:SQLserver procedureForum:ws-nlcomputer
Hoi Hugo

Het duurde even, wat andere zaken tussendoor gehad + mooie weer
en ik had niet verwacht dat je al per omgaande zou antwoorden :-)

>> Volgens de ANSI SQL-92 standaard wordt de gehele SELECT clause ineens gevalueerd
daardoor is het niet mogelijk om binnen n SELECT clause een expressie een alias
te geven en die alias elders in dezelfde clause weer te gebruiken. Access heeft
zich nooit aan dit deel van de standaard gehouden, wat in veel gevallen (zoals de
SQL die jij nu probeert te converteren) erg gemakkelijk is, maar ook gevaarlijk.

Dit was mij bekend ja, en heel gemakkelijk ook :-)
ik heb er nog nooit problemen mee ondervonden, maar kan
me ook wel situatie voorstellen met ongewenste resultaten
maar dan moet je wel 'gekke' dingen doen zoals in jou voorbeeld

> Gelukkig is er support voor derived tables - zeg maar
> het opnemen van een tijdelijke tabel in de FROM clause.

> Logisch gezien wordt eerst een tijdelijke tabel gemaakt
> met het resultaat van de subquery in de FROM-clause,
> waarna die in de uiteindelijke query wordt gebruikt.

daar was ik echt nooit opgekomen..

> Voor jouw query betekent dit dat we de IIf constructies
> moeten vervangen door equivalente CASE constructies,
> het geheel als derived table in een andere query moeten opnemen
> en dan kunnen we in die andere query wl verwijzen naar de aliassen
> (want dat zijn de kolomnamen van de derived table geworden).
> We krijgen dan dus zoiets als dit:

je bent geweldig, het werkte vrijwel meteen !

> dat dit ontwerp niet genormaliseerd is

't is weer een erfenisje uit een ander systeem
maar een aantal dingen kunnen nu gelijk verbeterd worden

> Verder is LONG geen datatype dat SQL Server kent.

De DDL kwam uit een Access hulptooltje...
in SQLserver gebruik ik de equivalent INT

> voor Buurt en Type zou ik CHAR nemen in plaats van VARCHAR

OK is nu aangepast (Buurt Char(2),Type Char(3))
Buurt is altijd 2, Type is 1-3 (meestal 1)

Tijdvak is momenteel (uit oude applicatie) een string 5 -10 maar
moet nog nader bekeken worden en wordt mogelijk ook een vaste lengte

> Ik hoop dat ik tussen alle wijze lessen door niet
> vergeten ben om ook je eigenlijk vraag te beantwoorden

Nee zeker niet, ik ben er weer ontzettend mee geholpen
ps overigens is de aanvullende info ook altijd welkom

Bedankt en groetjes --John



Bericht 4 van 4

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:09-08-2004
 Aan:John Kopmels (Sysop)MsgID:1455.4
 Onderwerp:SQLserver procedureForum:ws-nlcomputer
Hoi John,

> > Verder is LONG geen datatype dat SQL Server kent.
>
> De DDL kwam uit een Access hulptooltje...
> in SQLserver gebruik ik de equivalent INT

Ik vermoedde al zoiets. INT is in dit geval een prima keuze.

Ik ben blij te horen dat ik mijn raadgevingen je verder hebben geholpen!

Groetjes, Hugo