RE: Changing a table to have a new column before existing columns

["McCallion, Martin" <martin.mccallion@xxxxxxxxx>]

Elvis Budimlic wrote:

> Martin, if you are on V5R2, this might get you where you need to be:
> 
> CREATE TABLE lib/tblNew AS (SELECT f1, 0 AS newColumn, f2 
> FROM lib/tblOld)
> WITH DATA

Elvis, 

Thanks for that.  It won't update my table in place of course, but it
should be useful for creating a temporary copy of the data.

Except...

The syntax as you've given it works for me, but it creates the new
column as an integer.  I wanted a char(1), so I tried syntax like:

        CREATE TABLE lib/tblNew AS (SELECT f1, 0 CHAR (1) AS newColumn,
f2 
        FROM lib/tblOld)
         WITH DATA

and:

        CREATE TABLE lib/tblNew AS (SELECT f1, AS newColumn 0 CHAR (1),
f2 
        FROM lib/tblOld)
         WITH DATA

Both versions are invalid ("Token ( was not valid. Valid tokens: , FROM
INTO" in the former case; "Keyword CHAR not expected. Valid tokens: ,
FROM INTO"  in the latter).

So I tried changing it with ALTER TABLE ... ALTER COLUMN newColumn SET
DATA TYPE CHAR (1 ) NOT NULL; but that gives SQL0190, "Attributes of
column ...  not compatible"

However, I'm only now pursuing this out of academic interest (or
possible future use) and am pressing on with the CL solution.

Cheers,

Martin.

-- 
Martin McCallion
Senior Technical Consultant
Misys Wholesale Banking Systems
1 St George's Road, London, SW19 4DR, UK
T +44 (0)20 8486 1951
F +44 (0) 20 8947 3373
martin.mccallion@xxxxxxxxx
www.misys.com

This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are
virus free, however the recipient must take full responsibility for
virus checking. This email message is intended for the named recipient
only. It may be privileged and/or confidential. If you are not the
intended named recipient of this email then you should not copy it or
use it for any purpose, nor disclose its contents to any other person.
You should contact the Misys Banking and Securities Division as shown
below so that we can take appropriate action at no cost to yourself. 

Misys Banking and Securities Division, 1 St George's Road, Wimbledon,
London, SW19 4DR, England. Email: banking.postmaster@xxxxxxxxxx Tel: +44
(0) 20 8879 1188 Fax: +44 (0) 20 8947 3373