Re: Move fields in SQL created table

["Mark S. Waterbury" <mark.s.waterbury@xxxxxxx>]

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.
>