Re: CREATE TABLE question (SQL)

Hi Everyone,

I received several replies to this post, but unfortunately nothing I've
been able to use so far.

** SELECT INTO seems to be used for returning a single row -- I need to be
able to get multiple rows (and store them in a new table)

** INSERT INTO seems to require that the table receiving the rows has
already been created with a CREATE TABLE statement

** CREATE TABLE seems to require explicit definitions of the fields in the
new table (i.e. I can't say "create a table using CUSNAM from CUSFIL,
ORDNAM from ORDFIL, etc.")

** The closest thing to a solution was the suggestion to change the
session defaults in interactive SQL so that the result would be a new
table -- this works fine interactively, but I can't use it in embedded SQL
(and that's what my boss wants).

The bottom line is that it looks as if there is no way to store the
results of a SELECT statement in a new table that was created on the fly.
This is especially frustrating because it is so easy to do in M$ Access,
and it seems like such a handy feature.

Am I really missing something? Or (alternatively), is there a reason why
this feature is missing?

TIA,

Mike Naughton writes:
>My boss asked me this, and so far I haven't found an answer. Is it
>possible (on an AS/400) to create a new file (table) using SQL from and
>existing file (table)? I know it is possible in other environments -- for
>example, in M$ Access I can do it with:
>
>SELECT oldTable.* INTO newTable
>FROM oldTable
>WHERE (<some conditions are met>);
>
>but when I type STRSQL and try this on the AS/400 I get an error --
>apparently SELECT INTO means something different, and I can't figure out
>what else to try.
>
>Does anyone know if this can be done?
>
>TIA,


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---