Archives of the TeradataForum
Message Posted: Fri, 26 Oct 2007 @ 15:01:04 GMT
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')
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 "=").
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);
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|