Re: Help on SQL Server 7 to AS/400 conversion on V4R4

[Pete Hall <pbhall@xxxxxxxxxx>]

At 14:43 03/26/2000 , Kirk Goins wrote:
>I'm running V4R4. I can find "WITH DEFAULT" being valid on CREATE TABLE
>but not on ALTER TABLE. IF I'm reading the SQL manual correctly "WITH
>DEFAULT" is used to place a default value at table create time. In SQL
>SERVER 7, "DEFAULT" is in this case being used with an ALTER TABLE to set
>a value that will be inserted into a column if no value is specified on an
>"INSERT" token. DO I need a TRIGGER PGM to do this on the 400??

The V4R4 "DB2 UDB for AS/400 SQL Reference" documentation on ALTER TABLE 
shows a default clause in the column definition section. The documentation 
goes on to describe the default clause as follows:

  default-clause

      .-WITH-.
   |--+------+--DEFAULT--+-------------------------------------------------- 
|--+------+--DEFAULT--+---------------------------------------------------+->
                        +-constant------------------------------------------+
                        +-NULL----------------------------------------------+
                        +-USER----------------------------------------------+
                        +-CURRENT_DATE--------------------------------------+
                        +-CURRENT_TIME--------------------------------------+
                        +-CURRENT_TIMESTAMP---------------------------------+
                        '-cast-function-name--(--+-constant----------+---)--'
                                                 +-USER--------------+
                                                 +-CURRENT_DATE------+
                                                 +-CURRENT_TIME------+
                                                 '-CURRENT_TIMESTAMP-'

If it doesn't work like this, a call to IBM is probably in order. As I 
mentioned earlier though, be careful of the implicit data typing caused by 
the use of the literal value enclosed in apostrophes. I'd really expect 
this to work. Default values have always been supported by the database on 
the AS/400.

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---