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

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

Dieter


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

Attachments

Library

Quick Reference