13. maj 2005 - 08:33Der er
43 kommentarer og 1 løsning
Store problemer med SELECT
Jeg har store problemer med at lave mit udtræk korrekt.
Jeg prøver at vise en liste(koden virker 100%) over den tid en medarbejder har brugt på forskellige opgaver i løbet af en måned.
Jeg har i forvejen et udtræk der viser det samme for alle medarbejdere sammenlagt. Det virker 100%.
Men det udtræk jeg har lavet, enten glemmer at sætte de "tomme" dage ind, eller også sætter dem ind forkerte steder.
Min select for een opgave ser således ud: sql = "SELECT WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours " & _ "FROM workday WD2 LEFT JOIN (employeeday ED " & _ "INNER JOIN workday WD ON ED.workdayid=WD.id " & _ "INNER JOIN timespent TS ON TS.employeedayid=ED.id " & _ "INNER JOIN assignment A ON TS.assignmentid=A.id AND A.taskid=" & rs("id") & _ " and ts.t_hours>0) " & _ "ON WD2.id=WD.id " & _ "WHERE WD2.workmonthid=" & SQLType( CLng(arrValue(rpt_value_monthid)) ) & _ " and (A.employeeid=" & arrValue(rpt_value_empid) & " OR A.employeeid IS NULL) GROUP BY WD2.name, WD2.dato " & _ "ORDER BY WD2.dato"
Den bliver så kørt igennem for alle opgaver en given medarbejder har. Men hvorfor kommer de tomme dage ind forkert?? Jeg har siddet og bakset med det her i snart en måned, derfor de mange points. :-/ Sig endelig til, hvis jeg skal uddybe det mere.
Teknologi, AI og forretning er i centrum på Computerworlds Cloud og AI Festival i København d. 18. og 19. september. Se hele programmet for den store konference om strategisk brug af Cloud og AI på: www.cloud-festival.dk
Hvis din SQL virker når du kører den direkte på DBen, så er det nærliggende at fejlen ligger i din ASP side. Hvis du fjerner "OR A.employeeid IS NULL", kommer data så rigtigt ud på siden?
Hvor virker den der kun viser een medarbejder ikke? Er det både når du kører den direkte på databasen og fra din ASP side at den ikke giver det korrekte resultat?
Har du lavet en Responsen.Write(sql) og kørt resultatet direkte på databasen?
Hvordan ved du at resultatet er forkert? Har du da timer på en bruger en dag, hvor forespørgslen på alle medarbejdere intet viser?
En måde at verificere resultatet er at tilføje A.employeeid til "alle medarbejdere". Både i felt-delen og group by delen, og se hvad den returnerer for en udvalgt bruger.
Du behøver ikke spørge til om min kode virker. Det gør den. Jeg skal "bare" have rettet min sql så den bliver korrekt. Og som sagt virker sql'en til alle medarbejdere 110 % uanset om der er nogle tider i perioden eller ej.
Ah.. blank lørdag. Nå. Men når du kører din sql direkte på serveren, har du set at det er det forkerte resultat det returnerer?? For jeg er tilbøjelig til at tro at det er din kode som ikke håndterer NULL tothours korrekt. Har du set dage uden timer for "alle medarbejdere"?
Det gør ingen forskel, udover at der nu står 0 de steder hvor der ikke stod noget...
Jeg har også lige tjekket(igen) hvad sql'en giver direkte på db'en, og resultatet er noget underligt... Nogle af opgaverne komme ok ud, andre gør ikke!?
"Har du set dage uden timer for "alle medarbejdere"?" Ja, de dage hvor der ikke er nogen på arbejde...
Fredag er altså med nu!!, hvilket er løsningen på din kommentar "21/05-2005 09:39:14" Hvis dette er korrekt, så kan din kode ikke håndtere NULL værdier rigtigt.
Så vidt jeg kan læse af dit svar, så får du nu : opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag opgave 1, 5.5, 4, 6, 3.5, 0, 2, 5.5, 7.5
I stedet for: opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag opgave 1, 5.5, 4, 6, 3.5, , 2, 5.5, 7.5 som du forventer, og ikke opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag opgave 1, 5.5, 4, 6, 3.5, 2, 5.5, 7.5 som du fik tidligere.
Coalesce() erstatter NULL med en given værdi, her 0.
Derudover skriver du i sidste kommentar at nogle opgave kommer underligt ud. Dette kan godt skyldes "fejl" i data. Men hvis du kan vise tabeldefinitionerne for de relevante tabeller, så kan jeg da godt lige se på det.
Tabellerne kommer her: CREATE TABLE employeeday ( id int8 NOT NULL, employeeid int8 NOT NULL, workdayid int8 NOT NULL, arrival timestamp, departure timestamp, CONSTRAINT emplyeeday_id PRIMARY KEY (id), CONSTRAINT employee_fk FOREIGN KEY (employeeid) REFERENCES employee (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT workday_fk FOREIGN KEY (workdayid) REFERENCES workday (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS;
CREATE TABLE "assignment" ( id int8 NOT NULL, taskid int8 NOT NULL, employeeid int8 NOT NULL, deletedate varchar(23), CONSTRAINT assignment_id PRIMARY KEY (id), CONSTRAINT employee_fk FOREIGN KEY (employeeid) REFERENCES employee (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT task_fk FOREIGN KEY (taskid) REFERENCES task (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS;
CREATE TABLE timespent ( id int8 NOT NULL, assignmentid int8 NOT NULL, employeedayid int8 NOT NULL, t_hours numeric(4,2), a_hours numeric(4,2), approved varchar(1) NOT NULL DEFAULT 0, CONSTRAINT timespent_id PRIMARY KEY (id), CONSTRAINT assignment_fk FOREIGN KEY (assignmentid) REFERENCES "assignment" (id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT employeeday_pk FOREIGN KEY (employeedayid) REFERENCES employeeday (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS;
CREATE TABLE workday ( id int8 NOT NULL, name varchar(255), workmonthid int8 NOT NULL, dato date, CONSTRAINT workday_id PRIMARY KEY (id), CONSTRAINT workmonthid_fk FOREIGN KEY (workmonthid) REFERENCES workmonth (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS;
Hvor det med min gamle sql blev således: opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag.... opgave 1, 5.5, 4, 6, 3.5, 2, 5.5, 7.5, , ,....
Begge dele er lige forkerte.
Korrekt ville se således ud: opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag.... opgave 1, 5.5, 4, 6, , 3.5, 2, 5.5, 7.5, , ,....
Nå. Se det er jo noget helt andet. Der kommer jo rigtige blanke dage ud!! Undtagen for en specifik fredag. Mja, det var jo bare et eksempel...nogle opgaver er helt anderledes.
Men det kommer sådan ud: Eksempel: opgave, mandag, tirsdag, onsdag, torsdag, fredag, mandag, tirsdag, onsdag, torsdag, fredag.... opgave 1, 5.5, 4, 6, 3.5, 2, 5.5, 7.5, , ,.... opgave 2, 2, 4, 3.5, 5, 7.5, , ,.... opgave 3, 1, 4, 6, 5, , , , , , ,.... <- disse tommer felter er IKKE bare flyttet bagved de nævnte timer - det er nogle andre tomme felter, hvis du forstår :) ....
1) Er den manglende fredag en arbejdsdag? Dvs. findes den i Workday tabellen? Alle dage i øverste linie er i workday tabellen.
2) Er der andre fredage, hvor der ingen timer er på en employee? Hvordan bliver disse fredage vist? Som sagt behøver det ikke være en fredag, men det kan være alle dage. Men ja, der kan sagtens være flere dage hvor der ikke er registreret noget(kender du begrebet ferie? ;).
Jeg kender nemlig godt til ferie.. Selvom jeg får får lidt af det. Men nu har vi da fået afklaret at der for den specifikke fredag er workday data.
Nu observerer jeg at blanke datoer er fjernet, hvis der er timer efterfølgende. Er det korrekt? Hvis der er sådan, er disse blanke datoer flyttet bagud i listen? Dvs. er der altid det samme antal dage pr opgave i din liste? For så er det et sorteringsproblem.
Og det første man bliver blind på er øjnene ;-) Du bliver ved med at vise data pr. OPGAVE. Men den sql du har vist er en sum pr. dag!! Kan jeg ikke lige få vist den rigtige sql?
Gad vide om postgres laver sjov med os. Når du udtrækker alle brugere har du en Count(ED.id) med i din felt liste. Prøv for en lille test skyld at tilføje denne til din sql: SELECT WD2.name, WD2.dato, Sum( TS.a_hours ), Count(TS.id) as antalregistreringer
Nu observerer jeg at blanke datoer er fjernet, hvis der er timer efterfølgende. Er det korrekt? Hvis der er sådan, er disse blanke datoer flyttet bagud i listen? Dvs. er der altid det samme antal dage pr opgave i din liste? For så er det et sorteringsproblem. Blanke datoer?? Hvor ser du dem henne?? Der er ingen blanke datoer i systemet i den forstand at alle datoer som skal bruges er oprettet. Ellers ved jeg ikke liige hvad du tænker på...
Dagene oprettet udfra måneder, således at dagene passer til deres datoer. Vi har funktion der opretter hele måneder af gangen, dvs. ALLE dage i en given måned findes altså i systemet - også helligdage.
Du bliver ved med at vise data pr. OPGAVE. Men den sql du har vist er en sum pr. dag!! Ja, det skal summeres pr. dag. pr opgave. Derfor bruges også et "...a.taskid=" & rs("id") Hele sql-sætningen ligger inde i en løkke der kører alle opgaverne igennem.
Kan jeg ikke lige få vist den rigtige sql? Den rigtige? Mener du den til alle medarbejdere:
sql = "SELECT Count(ED.id) as antalmedarb, WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours " & _ "FROM workday WD2 LEFT JOIN (employeeday ED " & _ "INNER JOIN workday WD ON ED.workdayid=WD.id " & _ "INNER JOIN timespent TS ON TS.employeedayid=ED.id " & _ "INNER JOIN assignment A ON TS.assignmentid=A.id AND A.taskid=" & rs("id") & _ " ) " & _ "ON WD2.id=WD.id " & _ "WHERE WD2.workmonthid=" & SQLType( CLng(arrValue(rpt_value_monthid)) ) & _ " GROUP BY WD2.name, WD2.dato " & _ "ORDER BY WD2.dato"
Du skal ikke udskrive "antalregistreringer", det er bare for at fremtvinge en reel værdi på dage uden registreringer. nå, ok. Det gør ingen forskel, det er det samme.
Men måske er der noget med længden af navnet. Prøv noget kortere, Fx antalreg Ingen forskel - virker stadig ikke
Så du frasorteret weekend og helligdage i din kode!?. Nej, de skrives også ud til skærmen.
Hvordan ser opgave1 ud når du kører sqlen direkte på serveren? Er det: 5.5, 4, 6, 3.5, 2, 5.5, 7.5, NULL, NULL,... eller 5.5, 4, 6, NULL , 3.5, 2, 5.5, 7.5, BLANK , NULL, ...
Prøv de 2 følgende ændringer: 1) Ret feltlisten til og se om det giver det rigtige resultat. SELECT Count(ED.id) as antalmedarb, WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours
2) Dette er lidt af et gæt!
Prøv at rette: "FROM workday WD2 LEFT JOIN (employeeday ED " & _ "INNER JOIN workday WD ON ED.workdayid=WD.id " & _
Til: "FROM workday WD2 LEFT JOIN (workday WD " & _ "INNER JOIN employeeday ED ON ED.workdayid=WD.id " & _
Hvilken version af Postgres kører dette på?
Jeg er tæt på at dømme dette til at være en bug i Postgres
Problemet er at der rigtig nok kommer tomme dage med i udtrækket. Men desværre ikke hvis de er efterfulgt af dage med timer. Dette vurderer jeg til umiddelbart at være en bug, da der ikke sorteres på tothours.
Og du er sikker på at der ikke er samme problematik med alle medarbejdere?
Du kører med seneste version af 8?
For at komme så tæt på alle medarbejdere mht. summering, kan du prøve at omskrive din sql så selecteringen på medarbejder kommer ind i den "indre" tabel:
sql = "SELECT WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours " & _ "FROM workday WD2 LEFT JOIN (employeeday ED " & _ "INNER JOIN workday WD ON ED.workdayid=WD.id " & _ "INNER JOIN timespent TS ON TS.employeedayid=ED.id " & _ "INNER JOIN assignment A ON TS.assignmentid=A.id AND A.taskid=" & rs("id") & _ " and ts.t_hours>0" & _ " and A.employeeid=" & arrValue(rpt_value_empid) & ") " & _ "ON WD2.id=WD.id " & _ "WHERE WD2.workmonthid=" & SQLType( CLng(arrValue(rpt_value_monthid)) ) & _ " GROUP BY WD2.name, WD2.dato " & _ "ORDER BY WD2.dato"
"Og du er sikker på at der ikke er samme problematik med alle medarbejdere?" 110 % sikker - påskedagene er med på skemaet og der er ingen timer i de dage...
"Du kører med seneste version af 8?" Øh, 8.0 er vel 8.0? Jeg ved ikke om det er den sidste - jeg hentede den for ca. 1½-2 måneder siden.
Jeg prøver den sql på mandag - jeg har weekend fra idag af. :p
Hmmm... jeg kommer liige til at tænke på noget.... :) Der er vel ingen grund til at kører med den aller nyeste version på min test server, hvis den version der kører sitet(live) har en tidligere version...vel? ;)
Du har reddet ikke bare min dag, men min måned!! :):)
Jeg bukker og takker. :)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.