[DUG]: sql... now i really need help

Tracey Maule Thu, 13 Feb 2003 14:59:34 -0800

oh dear, no answers... not surprised

now instead of only returning the one same record every loop through, it is showing ALL possible records even if they arent related:

JOB SKILL REQUIRED

-------------------------------------------------------------------------------------------------------------------

SUPERVISORS

HAZ SUB

FARTING

LONG TIME

GHOSTING

FORKLIFT LICENCE

FIRST AID CERT

FORKLIFT DRIVER

HAZ SUB

FARTING

LONG TIME

GHOSTING

FORKLIFT LICENCE

FIRST AID CERT

could someone please look at my dodgy code and tell me why this is not working?? this should be reading:

JOB SKILL REQUIRED

-------------------------------------------------------------------------------------------------------------------

SUPERVISORS

HAZ SUB

FARTING

LONG TIME

GHOSTING

FORKLIFT LICENCE

FIRST AID CERT

FORKLIFT DRIVER

HAZ SUB

FIRST AID CERT

and heres my suspect code:

begin
            with IBQuery2 do
            begin
                Close;
                sQL.Clear;
                SQL.Add('select s.name, s.skillid');
                SQL.Add('from skill s');
                SQL.Add('join skillneeded sn on sn.skillid = s.skillid');
                SQL.Add('where sn.employmentid = :employmentid');
                Prepare;
            end;

            with IBQuery1 do
            begin
                Close;
                SQL.Clear;
                SQL.Add('Select name from person where personid = :personid');
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];
                Open;
                sPerson := FieldByName('Name').AsString;
                Str.Add('<tr><th><h3>Name</h3></th><th><h3>Job</h3></th><th><h3>Skill Required</h3></th><th><h3></h3></th></tr>');
                Str.Add('<tr><td colspan=4><b>' + sPerson + '</b></td></tr>');
                Close;
                SQl.Clear;
                SQL.Add('Select e.name, e.employmentid');
                SQL.Add('from employment e');
                SQL.Add('join personemployment pe on pe.employmentid = e.employmentid');
                SQL.Add('where pe.personid = :PersonID');
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];
                open;
                First;
                while not eof do
                begin
                    sEmploymentID := FieldByName('EmploymentID').AsString;
                    Str.Add('<tr>');
                    Str.Add('<td></td><td><font size=3>' +
                        FieldByName('Name').AsString +
                        '</font></td><td></td><td></td></tr>');
                    IBQuery2.ParamByName('EmploymentID').AsString :=
                        sEmploymentID;
                    IBQuery2.Open;
                    IBQuery2.First;
                    while (not IBQuery2.Eof) do
                    begin
                        Str.Add('<tr><td></td><td></td><td><font size=2>');
                        Str.Add(IBQuery2.FieldByName('Name').AsString);
                        Str.Add('</font></td><td></td></tr>');
                        IBQuery2.Next;
                    end;

next;
end;

end;
end

Oh, and important point... WHEN I RUN THE QUERY IN IBADMIN WITH THE EMPLOYMENTID HARD-ENTERED, IT RETURNS EXPECTED RECORDS. I HAVE EVEN PRINTED OUT THE EMPLOYMENTID TO THE REPORT AND IT IS THE CORRECT VALUE THROUGH THE LOOPS.

probably something stupid.....

----- Original Message -----

From: Tracey Maule

To: Multiple recipients of list delphi

Sent: Thursday, February 13, 2003 1:59 PM

Subject: [DUG]: sql... could someone tell me why please??

I was having a problem with nested queries.. where the first one was

               Select distinct e.name, e.employmentid
               from employment e
               join personemployment pe on pe.employmentid = e.employmentid
               where pe.personid = :PersonID
                ParamByName('PersonID').AsString :=
                    Request.QueryFields.Values['person'];

then i did this:

while not eof do

//display e.name

query2:

                select s.name
                from skill s
                join skillneeded sn on sn.skillid = s.skillid
                where sn.employmentid = :employmentid

query2.open;
while not query2.eof do

show s.name

this was showing some weird results.. ie the second query was returning only one record, and it was repeated under every employment name (even tho it had no relation to the query1 record)

I banged my head for ages, then on a whim removed the distinct from my first query, now all is well.

Why????????????????????

Tracey
Software Developer / Web Master
Logis
[EMAIL PROTECTED]
(025) 213-1065

[DUG]: sql... now i really need help

Reply via email to