Insert From Select

[mmarkows]

Hi,
I try to select data from one table and insert into another using one query. I've made it like this:

INSERT INTO z_conversion01 (emp-no, month, year, comp-amt)
    SELECT emp-no, month, year, SUM(comp-amt)
     FROM   p_sal_comps
     WHERE  year > 2006
     AND comp-no IN (403, 409, 465, 467)
     GROUP BY emp-no, month, year.

but nothing happens (no rows inserted). Debugger says the syntax is correct and I can insert into this table in the 'normal' way using

INSERT INTO z_conversion01 (emp-no, month, year, comp-amt) 
VALUES(100,11,2001,5432).

When I run SELECT statement alone, it returns number of records and the fields have correct data type.

Should I somehow commit the transaction or do it in the other way? When I try to use explicit COMMIT, I get an error "** Unknown Field or Variable name - COMMIT. (201)".

Thanks in advance for help.
Regards,
Maciej.

 

[Tarby777]

It looks like your problem might be with a bit of the program that you didn't show us; the COMMIT statement. Does the program generate any errors if you try it without the COMMIT? It might just be that you're not terminating one of your statements properly...

 

[mmarkows]

No, this is a stanalone query from the "query window". I know that it is a must to commit such operation in other RDBMSes. I do not know how to commit in PROGRESS (I get the error described above). I write the query, press F1, database performs calculations, and finishes. After that I try to query target table (SELECT * FROM...), and it returns 0 rows. If I issue only SELECT statement on the source table,

SELECT emp-no, month, year, SUM(comp-amt)
     FROM   p_sal_comps
     WHERE  year > 2006
     AND comp-no IN (403, 409, 465, 467)
     GROUP BY emp-no, month, year.

it returns about hundred rows.

 

[TomBascom]

If I understand you properly you're doomed to be frustrated because you're trying to use SQL within the 4GL editor.

SQL and 4GL don't mix and match well. If you're going to use SQL you should probably be using SQL-92 via ODBC/JDBC.

The 4gl does have limited, albeit deprecated, SQL-89 support. So SELECT, for instance, is supported syntax. The 4gl transaction model is, however, radically different from SQL's and COMMIT is nonsensical to the 4gl. (The 4gl scopes transaction to blocks and commits them automatically. No explicit commit is needed or even available -- instead there is UNDO.)

Using the 4GL you might accomplish your task with code such as this:

FOR EACH p_sal_comps NO-LOCK WHERE year > 2006:

  DO FOR z_conversion01 TRANSACTION: /* limit transaction scope to this block */

    CREATE z_conversion01.
    BUFFER-COPY p_sal_comps TO z_conversion01.

  END.  /* transaction commits automatically here */

END.

(You could skip the DO TRANSACTION block -- in which case default record and transaction scoping rules would take over.)

Apologies if I didn't translate the SQL correctly -- I'm a bit rusty ;-)

 

[mmarkows]

Tom, I've found the solution (error in fact), and I have to clarify this topic. (Un)fortunately that's not true that Progress does not support "INSERT FROM SELECT" statement. Progress'es documentation (Progress SQL 89 Guide and Reference, page 61) says:

"The INSERT INTO statement inserts one or more rows into an existing table. The data you insert can be a list of values that you supply or values from another table. (...) You can also insert values from another table by using a SELECT statement within the INSERT INTO statement."​

This description is followed by few examples consistent with my code.
My problem was too small "Lock Table Buffer" (I was processing large amount of data) and so I got the following error: "Lock Table Overflow, increase -L on Server(915)". The reason that I did not see the error message was very simple - I was using terminal client instead of Windows application and it did not show anything after processing.​

The solution was to execute query for smaller portions of data or increase "-L" value on the server.​

Hope this helps others to use SQL inside PROGRESS.​

Regards,
Maciej.​

 

[TomBascom]

I didn't actually say that "INSERT INTO" (or INSERT FROM depending on which part of your post one is reading) wouldn't work.

I'm glad that you've found a solution.

But I stand by my statement that that you're doomed to be frustrated Using SQL-89 inside the 4GL isn't a good long term strategy unless you're a real glutton for punishment. IMHO.