Re: V7R1 DB2 for IBMi; ALTER Table cc ADD COLUMN xx FOR COLUMN yy

On 28 Nov 2012 16:24, Eric Lehti wrote:

We have been on V7R1 for a week now, having upgraded from V5R4M5.

Issued this SQL command:
ALTER TABLE F55JOBS
ADD COLUMN COUNTRY FOR COLUMN WJCTR
CHAR ( 3)
NOT NULL WITH DEFAULT ' '

In SQL both of these statements return data:

This works: SELECT WJCTR FROM F55jobs

And this works: SELECT country FROM F55jobs

Is this new functionality?

Nothing new. The FOR COLUMN syntax has been supported on both the CREATE TABLE statement and the ALTER TABLE ADD COLUMN statement for as long as I can recall; always, AFaIK. The following snippet of the Syntax Diagram from both statements, is as follows, from as far back as the v4r5 documentation:

column-definition

|---column-name---+-------------------------------------+--->
| .-COLUMN-. |
'-FOR-+--------+--system-column-name--'

There is corresponding support via DDS using the ALIAS (Alternative Name) Keyword for Physical and Logical Files.

Note: I recall only that SQL TRIGGER have a restriction with regard to use of the system-column-name vs the column-name, nothing else I can recall.