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

Pete Hall <pbhall@execpc.com>
Sent by: owner-midrange-l@midrange.com
03/26/00 09:14 AM
Please respond to MIDRANGE-L

 
        To:     MIDRANGE-L@midrange.com
        cc: 
        Subject:        Re: Help on SQL Server 7 to AS/400 conversion on V4R4

At 22:04 03/25/2000 , Kirk Goins wrote:
>  ALTER TABLE COPYLMS.ACTIVITYTYPE
>         ADD CONSTRAINT DF_ACTIVITYTYPE_OVERRIDECREDITCOMPANY_ID
>         WITH DEFAULT '0' FOR OVERRIDECREDITCOMPANY_ID,
>
>         ADD CONSTRAINT DF_ACTIVITYTYPE_OVERRIDECREDITLOCATION_ID
>         WITH DEFAULT '666' FOR OVERRIDECREDITLOCATION_ID,
>
>         ADD CONSTRAINT PK__ACTIVITYTYPE__69C6B1F5 PRIMARY KEY
>         (
>                 ACTIVITYTYPE_ID
>         ) WITH  FILLFACTOR = 90
>;
>
>The AS/400 is complaining about the WITH DEFAULT with error msg
>
>SQL0199  30    1486  Position 2 Keyword WITH not expected. Valid tokens:
>                      CHECK UNIQUE FOREIGN PRIMARY.
>
>According to the book I have...
>"DEFAULT is a constraint that determines which value is inserted into the
>column if no value is specified for the column on an insert"
>
>How do I do this on the AS/400??

>If these are numeric columns (probably integer), try removing the 
>apostrophes around the literal values. Their use indicates that the 
target 
>column is alpha. You could also use one of the cast functions, but I 
>wouldn't think that would be necessary. This is just a guess but the WITH 

>DEFAULT clause is valid on the AS/400. What version of OS400 are you 
running?

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






---------------------------------
Kirk Goins
IBM Certified AS/400 Technical Solutions
Pacific Information Systems - An IBM Premier Business Partner
503-290-2104
kirkg@pacinfosys.com
---------------------------------
+---
| 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
+---