AW: Move fields in SQL created table

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Mark,

... but what about logical files, views, indexes, constraints and triggers
built over the physical file?

CHGPF and ALTER TABLE both will consider them. 
But ALTER TABLE only allows to add new columns at the end of the field list.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mark S. Waterbury
Gesendet: Tuesday, 16. March 2010 22:30
An: Midrange Systems Technical Discussion
Betreff: Re: Move fields in SQL created table

Hi, Mike:

You could do something like this:

CREATE TABLE TGTLIB/NEWTABLE2 AS (                     
    SELECT COLUMN1, COLUMN2, COLUMN3,                             
           COLUMN4, COLUMN5, COLUMN6
    FROM TGTLIB/OLDTABLE1
    ORDER BY COLUMN1, COLUMN2)                               
    WITH DATA;                                             
DROP TABLE TGTLIB/OLDTABLE1;
RENAME TABLE TGTLIB/NEWTABLE2 TO SYSTEM NAME OLDTABLE1;

If you also want to rename some (or all) of the columns, do it like this:

CREATE TABLE TGTLIB/NEWTABLE2 AS (                     
    SELECT COLUMN1 AS NEWFIELD1,                              
      COLUMN2 AS NEWFIELD2,
      COLUMN3 AS NEWFIELD3,
      COLUMN5 AS NEWFIELD5,
      COLUMN4 AS NEWFIELD4,
      COLUMN6 AS NEWFIELD6
    FROM TGTLIB/OLDTABLE1
    ORDER BY COLUMN1, COLUMN2)                               
    WITH DATA;                                             
DROP TABLE TGTLIB/OLDTABLE1;
RENAME TABLE TGTLIB/NEWTABLE2 TO SYSTEM NAME OLDTABLE1;

Cheers,

Mark S. Waterbury

> Mike Cunningham wrote:
> Is there a way to move a column(field) in an sql created table(file)?  In
DDS I would just rearrange the fields in the source and do a CHGPF and the
fields moved. In iSeries Navigator I can move a column around before I
create the table but once it exists it does not let me move the columns up
or down.
>