Temporary Tables / FOREACH 
Author Message
 Temporary Tables / FOREACH

Everyone,

I am trying to port some Informix 4gl Code(Logic)  to Delphi.

In the 4gl, I use a select cursor and a foreach statement similar to the following...
        DECLARE Cursor c_incl FOR
                SELECT invoice_no, control_key, "Y" flag FROM invoices
                INTO TEMP inclusions

        FOREACH c_incl
                UPDATE inclusions SET flag="N"
                WHERE control_key in (SELECT control_key FROM customers
                        WHERE county != "PHIL" )
        END FOREACH

In Delphi, I cannot select into a temp table, I cannot do a foreach (Unless I do a
 while not Table1.EOF do...), and I cannot do a subquery to update the rows in any
table (temporary or otherwise).

Does anyone know of a workaround to this problem ??

TIA,

John Cokos



Tue, 16 Mar 1999 03:00:00 GMT  
 Temporary Tables / FOREACH


Quote:

>Everyone,

>I am trying to port some Informix 4gl Code(Logic)  to Delphi.

>In the 4gl, I use a select cursor and a foreach statement similar to the following...
> DECLARE Cursor c_incl FOR
>  SELECT invoice_no, control_key, "Y" flag FROM invoices
>  INTO TEMP inclusions

> FOREACH c_incl
>  UPDATE inclusions SET flag="N"
>  WHERE control_key in (SELECT control_key FROM customers
>   WHERE county != "PHIL" )
> END FOREACH

>In Delphi, I cannot select into a temp table, I cannot do a foreach (Unless I do a
> while not Table1.EOF do...), and I cannot do a subquery to update the rows in any
>table (temporary or otherwise).

>Does anyone know of a workaround to this problem ??

I can't see the point in your cursor, unless, maybe you're trying to avoid
some sort of locking issue?

Why not just:

   with SomeQuery do
     begin
     SQL.Clear;
     SQL.Add('SELECT invoice_no, control_key, "Y" flag FROM invoices');
     SQL.Add('INTO TEMP inclusions');
     ExecSQL;
     SQL.Clear;
     SQL.Add('UPDATE inclusions SET flag="N"');
     SQL.Add('WHERE control_key in (SELECT control_key FROM customers');
     SQL.Add('WHERE county != "PHIL" )');
     ExecSQL;
     end;

I've not had any problems with temporary tables -- unless you're trying to
do all this with SQL-crippled Paradox or dbase files...!!

Cheers,
Kerry S
------------------------------------,------------------------------------------

Quanta Systems, Auckland            | http://www.iiug.org/techinfo/faq/
New Zealand. Work: +64 9 377-4473   |  ftp://ftp.iiug.org/pub/informix/faq
             Home: +64 9 279-3571   |  ftp://kcbbs.gen.nz:/pub/informix/



Mon, 22 Mar 1999 03:00:00 GMT  
 Temporary Tables / FOREACH

Quote:



> >Everyone,

> >I am trying to port some Informix 4gl Code(Logic)  to Delphi.

> >In the 4gl, I use a select cursor and a foreach statement similar to the following...
> > DECLARE Cursor c_incl FOR
> >  SELECT invoice_no, control_key, "Y" flag FROM invoices
> >  INTO TEMP inclusions

> > FOREACH c_incl
> >  UPDATE inclusions SET flag="N"
> >  WHERE control_key in (SELECT control_key FROM customers
> >   WHERE county != "PHIL" )
> > END FOREACH

> >In Delphi, I cannot select into a temp table, I cannot do a foreach (Unless I do a
> > while not Table1.EOF do...), and I cannot do a subquery to update the rows in any
> >table (temporary or otherwise).

> >Does anyone know of a workaround to this problem ??

> I can't see the point in your cursor, unless, maybe you're trying to avoid
> some sort of locking issue?

> Why not just:

>    with SomeQuery do
>      begin
>      SQL.Clear;
>      SQL.Add('SELECT invoice_no, control_key, "Y" flag FROM invoices');
>      SQL.Add('INTO TEMP inclusions');
>      ExecSQL;
>      SQL.Clear;
>      SQL.Add('UPDATE inclusions SET flag="N"');
>      SQL.Add('WHERE control_key in (SELECT control_key FROM customers');
>      SQL.Add('WHERE county != "PHIL" )');
>      ExecSQL;
>      end;

> I've not had any problems with temporary tables -- unless you're trying to
> do all this with SQL-crippled Paradox or dbase files...!!

> Cheers,
> Kerry S
> ------------------------------------,------------------------------------------

> Quanta Systems, Auckland            | http://www.iiug.org/techinfo/faq/
> New Zealand. Work: +64 9 377-4473   |  ftp://ftp.iiug.org/pub/informix/faq
>              Home: +64 9 279-3571   |  ftp://kcbbs.gen.nz:/pub/informix/

I also use temp tables but I want to do some other processing and
formatting with them so I set up a query or ttable and use a BatchMove
to move the records I want into a temp table.  I only do this when I
want to do some messing around with the tables, usually for reports.
And you're right about Paradox being SQL-crippled, if you are using
paradox tables, don't get fancy with your SQL statements . .thou shall
get bit!

    or is that Shalt??   Oh, well . .I'll never get the hang of middle
english :-) . .what do you expect from a Brooklynite

   Ted Herrlich
--

"The Pen is Mightier than the Sword, unless you are One on One."



Mon, 22 Mar 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Help! Native language in Temporary tables

2. Help Requested: Temporary DataSets/Tables...

3. How to create temporary table for each user?

4. Add temporary index to a table

5. temporary table resource limit

6. Sybase, Delphi and temporary tables?

7. Temporary Table in Delphi, like :PRIV:Answer.db in pdox

8. Temporary tables

9. Temporary files with Paradox tables

10. TQuery and temporary table (urgent)

11. Sybase Temporary Tables

12. FPC: Wrong behavior in nested use of TCollection.ForEach

 

 
Powered by phpBB® Forum Software