Archives of the TeradataForum

Message Posted: Mon, 18 Mar 2013 @ 20:00:12 GMT

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

I want to use the Merge statement (in a Macro) but only to Insert certain Columns, leaving the Nullable Columns alone (i.e. not to have to mention them)

Take this cut-down example,

     CREATE MULTISET TABLE bookingorder_test1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           COM_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'WW',
           BUL_ID CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'MA2  ',
           ORD_ID CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           ORD_SRKY_ID INTEGER NOT NULL,
           ORD_SRCE_SYSTEM_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'MB',
           ORD_SEQ_NR BYTEINT  NULL COMPRESS 1 ,
           ORD_CONT_VL DECIMAL(9,3)  NULL COMPRESS 0.000 ,
           ORD_CONT_WT DECIMAL(9,3)  NULL COMPRESS 0.000 )
     UNIQUE PRIMARY INDEX ORDER_UPI ( ORD_SRKY_ID );

If I mention all Columns then it's fine ...

     MERGE INTO BOOKINGORDER_TEST1 TGT
          USING (SELECT ORD_SRKY_ID ,COM_ID
          ,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          ,ORD_CONT_VL
          ,ORD_CONT_WT
           FROM BOOKINGORDER1)
          AS BO_IN (ORD_SRKY_ID, COM_ID,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          ,ORD_CONT_VL
          ,ORD_CONT_WT)
          ON BO_IN.ORD_SRKY_ID=TGT.ORD_SRKY_ID
          WHEN MATCHED THEN UPDATE
             SET ORD_CONT_VL=TGT.ORD_CONT_VL + 1
          WHEN NOT MATCHED THEN INSERT
          VALUES (
          BO_IN.COM_ID
          ,BO_IN.BUL_ID
          ,BO_IN.ORD_ID
          ,BO_IN.ORD_SRKY_ID
          ,BO_IN.ORD_SRCE_SYSTEM_CD
          ,BO_IN.ORD_SEQ_NR
          ,BO_IN.ORD_CONT_VL
          ,BO_IN.ORD_CONT_WT );

99674 processed

but if I when I leave out the Columns that I don't want to explicitly mention ...

     MERGE INTO BOOKINGORDER_TEST1 TGT
          USING (SELECT ORD_SRKY_ID ,COM_ID
          ,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          --,ORD_CONT_VL
          --,ORD_CONT_WT
           FROM BOOKINGORDER1)
          AS BO_IN (ORD_SRKY_ID, COM_ID,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          --,ORD_CONT_VL
          --,ORD_CONT_WT
     )
          ON BO_IN.ORD_SRKY_ID=TGT.ORD_SRKY_ID
          WHEN MATCHED THEN UPDATE
             SET ORD_CONT_VL=TGT.ORD_CONT_VL + 1
          WHEN NOT MATCHED THEN INSERT
          VALUES (
          BO_IN.COM_ID
          ,BO_IN.BUL_ID
          ,BO_IN.ORD_ID
          ,BO_IN.ORD_SRKY_ID
          ,BO_IN.ORD_SRCE_SYSTEM_CD
          ,BO_IN.ORD_SEQ_NR
          --,BO_IN.ORD_CONT_VL
          --,BO_IN.ORD_CONT_WT
     );

I get .... 3812 the positional assignment has too few values.

.. and true enough. But, I can't seem to do this :

     MERGE INTO BOOKINGORDER_TEST1 TGT
     (ORD_SRKY_ID ,COM_ID
          ,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR)
                       TGT
          USING (SELECT ORD_SRKY_ID ,COM_ID
          ,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          --,ORD_CONT_VL
          --,ORD_CONT_WT
           FROM BOOKINGORDER1)
          AS BO_IN (ORD_SRKY_ID, COM_ID,BUL_ID
          ,ORD_ID
          ,ORD_SRCE_SYSTEM_CD
          ,ORD_SEQ_NR
          --,ORD_CONT_VL
          --,ORD_CONT_WT
     )
          ON BO_IN.ORD_SRKY_ID=TGT.ORD_SRKY_ID
          WHEN MATCHED THEN UPDATE
             SET ORD_CONT_VL=TGT.ORD_CONT_VL + 1
          WHEN NOT MATCHED THEN INSERT
          VALUES (
          BO_IN.COM_ID
          ,BO_IN.BUL_ID
          ,BO_IN.ORD_ID
          ,BO_IN.ORD_SRKY_ID
          ,BO_IN.ORD_SRCE_SYSTEM_CD
          ,BO_IN.ORD_SEQ_NR
          --,BO_IN.ORD_CONT_VL
          --,BO_IN.ORD_CONT_WT
     );

as I get a syntax error.

Is the Merge statement really that restrictive ? Does it really demand ALL Columns be set to explicitly set to a value ?

Regards

David Clough
Senior BI Database Designer

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