Record with Null getting dropped for Nullable col in PX 7.5.

sharathkamathm · Post by **sharathkamathm** » Mon Apr 20, 2009 2:24 pm

Hi,

I have simple job with

Dataset ---> Transformer ---> Teradata Enterprise Stage

Ex: Null Record ---- 1, ABC, 0, NULL, 10

Job is completing with following warning:
"For Trg Col 'A' cannot load with Null so Record dropped."

i.e. Its loading all records except 3 records which has Null in Source col 'A' (Dataset) while loading Trg Col 'A' (Teradata) and all 3 records are getting dropped even though Trg Col 'A' is Nullable column. This is in PX with DS v7.5.2.

Note: In transformer i am doing 2 thing in stage variable
1. StgVar1 = DecimaltoString(Src col 'A', "suppresszero")
2. StgVar2 = if Len(Trim(StgVar1) = 0 then -1 else StgVar1

And after this loading StgVar2 to Trg Col 'A'

But as it was not loading to Trg, So I have tried in Transformer by
adding another stage variable with

3. StgVar3 = if isnull (StgVar2) then setnull() else stringtodecimal(StgVar2)

and then loading

Stagevariable to Trg col 'A'
(Trg col 'A' --> Decimal, len=38 and scale=10 with Nullable)....

But "StgVar3" didn't work.....So, Need help on the same....

chulett · Post by **chulett** » Mon Apr 20, 2009 2:27 pm

Stage variables do not support nulls.

sharathkamathm · Post by **sharathkamathm** » Mon Apr 20, 2009 2:36 pm

Please let me know wht changes that i should do to job but with retaining "StgVar1" and "StgVar2"...stage variables in transformer....
Also i need this record with Null to load from Src col 'A' (dataset) to Trg col 'A' (Teradata).....

dsuser_cai · Post by **dsuser_cai** » Mon Apr 20, 2009 4:16 pm

If you use oracle as source, then use a NVL function to conver the null values to something else, or i think you can use NullToEmpty or NullToValue function in the transformer.

sharathkamathm · Post by **sharathkamathm** » Tue Apr 21, 2009 12:46 am

Actually i am bring in Source with this couple of records with NULL and i need to push these record (with NULL) as it is in Target....and my soucre (Dataset) and Trg (Teradata Enterprise Stage).....

Note: In transformer i am doing 2 thing in stage variable
1. StgVar1 = DecimaltoString(Src col 'A', "suppresszero")
2. StgVar2 = if Len(Trim(StgVar1) = 0 then 1 else StringtoDecimal(StgVar1)

And after this loading stage variable "StgVar2" to Trg Col 'A'

(Trg col 'A' --> Decimal, len=38 and scale=10 with Nullable)....

but this is not working......

let me know if it is any issue with conversion i.e.
1) DecimaltoString()
2) StringtoDecimal()

or
can anyone help with any workaround to load this NULL record to Target as it is getting dropped!!!!

Pagadrai · Post by **Pagadrai** » Tue Apr 21, 2009 12:59 am

Hi,
Can you explain why you are doing DecimalToString and StringToDecimal?
I dont think these stage variables are required.

can you elaborate what you are trying to achieve?

Kryt0n · Post by **Kryt0n** » Tue Apr 21, 2009 1:19 am

What was wrong with the guidance you have already been given?

2 clear bits of advice were already given:
1) Stage Variables cannot hold nulls
2) Use help files to read about NullToValue

Can maybe do with reading about DecimalToString and StringToDecimal too...

Pagadrai · Post by **Pagadrai** » Tue Apr 21, 2009 2:34 am

Hi,
is your target column Nullable in database?
please verify that by inserting NULL values using a sql statement outside Datastage.

And when you do conversion functions, you need to handle NULL values.
If your requirement is just to load the source data to target without
any transformations, then I dont understand why you need those conversion functions.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 21, 2009 3:11 am

Disable operator combination to get a better idea (some idea) of which stage is throwing the errors.

dsuser_cai · Post by **dsuser_cai** » Tue Apr 21, 2009 11:23 am

Try to handle the null.. may be by using modify stage.. or try to get the rejected records in a text file and read from unix (from putty) the data might have some new line character, in that case try to use a chr function to suppress it.

For example, when u read the file from unix it should be like this.

'A','123','', 'hyw'

you should see only '' (two single quotes) for null value and if you see some space or a new line character then there is something else.. and we need to handle that differently.

Ramona Reed · Post by **Ramona Reed** » Tue Apr 21, 2009 1:02 pm

The following may help you:

stageVar1 = If IsNull(src A) Then "" else If Trim(src A) = "" Then "" else Trim(src A)

stageVar2 = StringToDecimal(stageVar1)

Handle the null values with the first stage variable, and work with the second stage variable for your fields.

sharathkamathm · Post by **sharathkamathm** » Thu Apr 23, 2009 3:12 am

Thanks to ALL, day before yesterday, I have resolved it by using all u ppl inputs and I did following changes and it worked,

In transformer i am doing 2 thing in stage variable,

1. StgVar1 = If IsNull(Src col 'A') then "#" else DecimaltoString(Src col 'A', "suppresszero")

2. StgVar2 = If StgVar1 = "#" then "#" Else if Len(Trim(StgVar1) = 0 then 1 else StringtoDecimal(StgVar1)

And after that load StgVar2 to Trg Col 'A',

3. Trg Col 'A' = If StgVar2 = "#" then SetNull() else StgVar2

Actually, i needed to retain both stage variable and error was Src record was with Null..n stage var unable to handle null....so issue got resolved and trg was loaded with Null!!!!