FOR SELECT LOOP

Thread View: borland.public.interbase.sql

5 messages

[T] Back to Threads [V] Tree View [G] Group Articles

5 total messages Started by Werner Debrie Sat, 23 Nov 2002 17:07

#11349

Author: Werner Debrie
Date: Sat, 23 Nov 2002 17:07

72 lines
3006 bytes

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

Re: FOR SELECT LOOP

#11350

Author: Arno Brinkman
Date: Sat, 23 Nov 2002 17:56

61 lines
2414 bytes

Hi,

> 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.
>
<snip>

It seems that you forgot that every "sub-query" should be in the BEGIN - END
block.
Try :

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

    /* First Total */
    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;

    /* Second Total */
    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;

    /* Thirth Total */
    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



Regards,
Arno Brinkman

Re: FOR SELECT LOOP

#11352

Author: Wayne Niddery [TeamB]
Date: Sat, 23 Nov 2002 19:48

32 lines
1921 bytes

"Arno Brinkman" <bnewsgroups @ abvisie.nl> wrote in message
news:3ddfc059@newsgroups.borland.com...
>
> It seems that you forgot that every "sub-query" should be in the BEGIN -
END
> block.
>   FOR SELECT DISTINCT Invoices.date
>       FROM Invoices
>       WHERE Invoices.month= '200209'
>       INTO :DAT
>   do
>   begin
[...]
>     /* Thirth Total */
>     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;

        SUSPEND;

>   end
> end


--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
It used to be that other people's achievements were considered an
inspiration, not a grievance.

Re: FOR SELECT LOOP

#11353

Author: Werner Debrie
Date: Sat, 23 Nov 2002 20:04

3 lines
1090 bytes

It works very well and ....fast!

Thank you very much!

Re: FOR SELECT LOOP

#11354

Author: Arno Brinkman
Date: Sun, 24 Nov 2002 00:29

19 lines
1446 bytes

Indeed Wayne,

> >     /* Thirth Total */
> >     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;
>
>         SUSPEND;
>
> >   end
> > end

Don't forget the suspend :-)

Regards,
Arno

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads