Archives of the TeradataForum

Message Posted: Fri, 26 Oct 2007 @ 15:01:04 GMT

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

Good Day,

Hope someone can help.

We want to implement a solution to prevent inserts of duplicate records into a SET table that has a NUPI based on a single column. The table has a PK consisting of multiple columns (some of which are nullable for very specific business reasons).

The solution we had in mind is to ensure that the "where" clause of the update portion of the mload can handle nulls for nullable columns, as follows:

     where Account_Num = :Account_Num(Dec(18, 0) , Format '999999999999999999.')
        and coalesce(Account_Modifier_Num, -32768) =
     coalesce(:Account_Modifier_Num, -32768)
        and coalesce(SB_Account_Open_Dt, date '9999-12-31') =
     coalesce(:SB_Account_Open_Dt, '9999-12-31')(Date ,Format 'YYYY-MM-DD')
        and coalesce(Balance_Category_Type_Cd, -32768) =
     coalesce(:Balance_Category_Type_Cd, -32768)
        and coalesce(Time_Period_Cd, -32768) = coalesce(:Time_Period_Cd, -32768)
        and coalesce(Account_Summary_Dt, date '9999-12-31') =
     coalesce(:Account_Summary_Dt, '9999-12-31')(Date ,Format 'YYYY-MM-DD')

(Account_Num is not nullable, and does not need a coalesce)

Using coalesce works fine for for all the columns in the above example, except the partition column (in this case Account_Summary_Dt). I suspect any nullable columns that are part of the PI might also be affected but this does not apply to this specific example, because Account_Num is the only PI column.

If the coalesce is used for the conditional test on the partition column, we get the following message:

3538: A MultiLoad UPDATE Statement is Invalid.

When we change:

     and coalesce(Account_Summary_Dt, date '9999-12-31') =
         coalesce(:Account_Summary_Dt, '9999-12-31')(Date ,Format 'YYYY-MM-DD')

back to:

     Account_Summary_Dt = :Account_Summary_Dt (Date ,Format 'YYYY-MM-DD')

then the error dissappears, but we are not handling possible null values (on either side of the "=").

Regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

PS: Partition column as defined on table

     PARTITION BY RANGE_N(Account_Summary_Dt  BETWEEN DATE '2003-01-01' AND
     DATE '2003-12-31' EACH INTERVAL '1' MONTH ,
     DATE '2004-01-01' AND DATE '2004-12-31' EACH INTERVAL '1' MONTH ,
     DATE '2005-01-01' AND DATE '2005-12-31' EACH INTERVAL '1' MONTH ,
     DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH ,
     DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH ,
       NO RANGE OR UNKNOWN);

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