RE: CREATE TABLE question (SQL)

[Chris Bipes <chris.bipes@xxxxxxxxxxxxxxx>]

The old command CRTDUPOBJ works great.  If you don't know the library,
RTVOBJD can return the library name.  No the INSERT INTO will work fine.
CPYF also has some basic record selection, but reads the entire file to
select the records.

Christopher K. Bipes    mailto:ChrisB@Cross-Check.com
Sr. Programmer/Analyst  mailto:Chris_Bipes@Yahoo.com
CrossCheck, Inc.        http://www.cross-check.com
6119 State Farm Drive   Phone: 707 586-0551 x 1102
Rohnert Park CA  94928  Fax: 707 586-1884

If consistency is the hobgoblin of little minds, only geniuses work here.
Karen Herbelin - Readers Digest 3/2000

-----Original Message-----
From: Mike Naughton [mailto:mnaughton@juddwire.com]
Sent: Wednesday, March 07, 2001 6:49 AM
To: RPG400-L@midrange.com
Subject: 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
+---
+---
| 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
+---