Default value with 'null' instead 'empty'

Database
Enthusiast

Default value with 'null' instead 'empty'

Hi mates, I need to know if is possible to make something that fix this:

 

I create a table, like the next one.

 

SyntaxEditor Code Snippet

CREATE MULTISET TABLE STAGEP.PRUEBALEX,
NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO
(
Column1 INTEGER DEFAULT 10
);

 

Now I do:

SyntaxEditor Code Snippet

INSERT INTO STAGEP.PRUEBALEX  VALUES ();

INSERT INTO STAGEP.PRUEBALEX  VALUES (null);

 

 First one works, with default value, second do not.

I fully understand why.

My problem becames when I use a fastload or tpump load strategy from Informatica Powercenter. Because if I map that column, and I got a null, it send 'null' instead of 'empty' which activates the DEFAULT clause.

I want to know if there is any way to make like a CASE to set 'null' to activate DEFAULT value. 

I try to add CONSTRAINS since I read on documentation :

A default value must not violate any CHECK constraints specified for the column. If a default value would violate a CHECK constraint, the conflict is not recognized at the time the table is defined or altered. The conflict is recognized the first time that an INSERT or UPDATE attempts to enter a NULL, which would be replaced by the default.

 

But add "NOT NULL" doesn't work either.


Thanks in advance.

2 REPLIES
Senior Apprentice

Re: Default value with 'null' instead 'empty'

Just a thought - which may not work...

 

Can you get Powercenter to send the DEFAULT keyword instead of NULL?

 

Without that I don't think you can do what you want in the load itself. You'd have to load the data then run an UPDATE command or maybe an INSERT/SELECT and change the data as part of the SELECT.

 

HTH
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Default value with 'null' instead 'empty'

I think same than you.

 

 

We also think in an UPDATE procedure, but its the same, we will need to modify the mappings on POWERCENTER to call this new function.

 

 

We're trying to avoid to modify a high amount of processes which got this issue, but seems we will need to add a Expresion on POWERCENTER to send the table DEFAULT value if he found a null.

 

 

 Thanks Dave!