Archives of the TeradataForum

Message Posted: Thu, 18 Apr 2002 @ 18:19:22 GMT

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

<-- Anonymously Posted: Thursday, April 18, 2002 13:43 -->

Please forgive any duplication of questions but I have a troublesome update I have a Multiload which input fields that may be null'd.

for example,

Given the following table definition

CREATE SET TABLE XYZTable,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      X CHAR(3) NOT NULL,
      Y CHAR(4),
      Z CHAR(1)
    )
unique primary index (x);

and the following FIELD setup in Multiload:

        .field IN_x * char(3);
        .field IN_y * char(4) NULLIF '    ';
        .field IN_z * char(1) NULLIF ' ';

...

and the following UPDATE statement in the same Multiload:

UPDATE XYZTable
        SET Y = :IN_y
                   ,Z = :IN_z
      WHERE X = :IN_x
            AND (Y <> :IN_y OR
                     Z <> :IN_Z)

The current table contains:

| X | Y  |Z|
|---|----|-|
|AAA|AAAA|A|
|BBB|BBBB|B|
|CCC|CCCC|C|

The input file contains

| X | Y  |Z|
|---|----|-|
|AAA|AAAA|A|
|BBB|BBBB|B|
|CCC|CCCC| |<-blank IN_z value nulled

When I do the update statement, the table stays the way it is rather than replacing the 'C' with a NULL (?).

Can anyone explain this behavior? Can anyone suggest a solution?

Puzzled

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