🚀 go-pugleaf

Somewhere in this newsgroup, a solution for merging two query's resulting in
one table (where the result of the queries is in different column of the
table) was to use a FOR SELECT LOOP in a procedure.

I tried this because it looked like it was the ideal solution for a problem
that I had.

So I tried this;

(Invoices is a table that hold the number and date of the invoices.
 InvoiceD holds the detail of the invoices and has a returncode field. I
want to make a total of the different returncodes for a period grouped by
date)


CREATE PROCEDURE TESTFORLOOP
RETURNS (
    TOTAL3 NUMERIC(11,2),
    TOTAL2 NUMERIC(15,2),
    TOTAL1 NUMERIC(15,2),
    DAT DATE)
AS
begin
  /* Procedure Text */
  FOR SELECT DISTINCT Invoices.date
      FROM Invoices
      WHERE Invoices.month= '200209'
      INTO :DAT
  do
  begin
    select SUM(InvoiceD.total)
    from Invoices Join InvoiceD ON Invoices.invnum = InvoiceD.number
    where Invoices.date = :DAT and InvoiceD.returnc = 1
    GROUP BY Invoices.date
    INTO :TOTAL1;
  end

  begin
    select SUM(InvoiceD.total)
    from InvoicesJoin InvoiceD ON Invoices.invnum = InvoiceD.InvoiceD.number
    where Invoices.date = :DAT and InvoiceD.returnc = 2
    GROUP BY Invoices.date
    INTO :TOTAL2;
  end

  begin
    select SUM(InvoiceD.total)
    from Invoices Join InvoiceD ON Invoices.invnum =
InvoiceD.InvoiceD.number
    where Invoices.date = :DAT and InvoiceD.returnc = 3
    GROUP BY Invoices.date
    INTO :TOTAL3;
  end
end

I end up with one row with the correct data, altough there have to be 19
rows.
If I use only the first subquery and at 'suspend' it works.
If I add suspend to all the subqueries , I just have one row
It's like the 2nd and 3rd subqueries aren't procesed (with or without
'suspend').

In the data definition guide the explanation for a FOR SELECT is:

repeats the statement or block following DO for every qualifying row
retrieved by the SELECT statement.

So it should work!

What am I doing wrong?

Werner Debrie