Record with Null getting dropped for Nullable col in PX 7.5.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Thu Apr 26, 2007 11:33 am
- Location: Bangalore
Record with Null getting dropped for Nullable col in PX 7.5.
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....
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....
-
- Participant
- Posts: 7
- Joined: Thu Apr 26, 2007 11:33 am
- Location: Bangalore
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
-
- Participant
- Posts: 7
- Joined: Thu Apr 26, 2007 11:33 am
- Location: Bangalore
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!!!!
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!!!!
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
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.
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.
Thanks
Karthick
Karthick
-
- Participant
- Posts: 7
- Joined: Thu Nov 17, 2005 7:22 am
-
- Participant
- Posts: 7
- Joined: Thu Apr 26, 2007 11:33 am
- Location: Bangalore
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!!!!
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!!!!