Archives of the TeradataForum

Message Posted: Wed, 27 Feb 2008 @ 13:56:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Firstly, thanks to Michael Larkins for his answers.

I do have a follow-up question :

Does anyone have an example of setting the Format of dates, times and timestamp Formats within an Upsert statement, or is it just not allowed? I read from the manual that the .FIELD definitions for Dates and Times have to be defined as Char. Ok, but how then does the Format get set ?

I've got this, but it fails on syntax :

     .LAYOUT MYTABLE1;
     .FIELD PK_COL1          * VARCHAR(255);
     .FIELD PK_COL2          * VARCHAR(255);
     .FIELD COL3             * VARCHAR(255);
     .FIELD COL4             * VARCHAR(255);
     .FIELD SOFTLOCK_TS      * VARCHAR(255);

     .DML LABEL labelA1
     DO INSERT FOR MISSING UPDATE ROWS
     SERIALIZEON (PK_COL1)
     ;
     UPDATE MYTABLE1
           SET
            COL3   = COALESCE(:COL3,' ')
           ,COL4   = COALESCE(:COL4,' ')
           ,SOFTLOCK_TS   = CAST(:SOFTLOCK_TS AS TIMESTAMP(6), FORMAT 'YYYY-MM-DD
           HH24:MI:SS.FF6')
           WHERE PK_COL1 = :PK_COL1
           AND PK_COL2 = :PK_COL2
           AND SOFTLOCK_TS <= CAST(:SOFTLOCK_TS AS TIMESTAMP(6), FORMAT 'YYYY-MM-DD
           HH24:MI:SS.FF6')
           ;
     INSERT INTO MYTABLE1 (
           PK_COL1
           ,PK_COL2
           ,COL3
           ,COL4
           ,SOFTLOCK_TS
           ) VALUES (
           :PK_COL1
           ,:PK_COL2
           ,COALESCE(:COL3,' ')
           ,COALESCE(:COL4,' ')
           ,CAST(:SOFTLOCK_TS AS TIMESTAMP(6), FORMAT 'YYYY-MM-DD HH24:MI:SS.FF6')
           ) ;

Dave Clough
Database Designer
Express ICS

www.tnt.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>