Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 01 Nov 2007 @ 12:54:51 GMT

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

Subj:   Re: Problem with handling null values for Partition
From:   Dieter Noeth

Etienne Stieger wrote:

  In our case we have a MULTISET table that has a NUPI based on a single column (Account_Num, which is not nullable). The table has a PK consisting of multiple columns (some of which are nullable for very specific business reasons). The partition index for the table is on column (Account_Summary_Dt, which is nullable), and this column is part of the PK, so it needs to be part of the "where" clause of the update statement.  


  We get the following error when using "coalesce" on the partition column:  

  3538: A MultiLoad UPDATE Statement is Invalid.  


  We have unsuccesfully tried various other approaches such as:  

  A) OR ColumnA is null AND :ColumnA is null (and variations thereof, using cast, etc)  

  B) Case statements  

It's MLoad, so you have to use the PI and partitioning columns in your WHERE-clause without any function/calculation on it.

But you can create two different DML LABELs, one for NOT NULL and one for NULL:

     .dml label date_null
     and Account_Summary_Dt is null

     .dml label date_not_null
     and Account_Summary_Dt = :Account_Summary_Dt

     apply date_null where :Account_Summary_Dt is null
     apply date_not_null where :Account_Summary_Dt is not null


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023