null handling
Moderators: chulett, rschirm, roy
null handling
Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns it is taking as null values and is rejected in the table.
Do I need to populate those columns with spaces by handlling null or is there any other options.
If my column length is 5 should I need to populate that column with 5 spaces.
I am using trim function for all the columns
Suggestions required please
Thanks in advance
Saik
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns it is taking as null values and is rejected in the table.
Do I need to populate those columns with spaces by handlling null or is there any other options.
If my column length is 5 should I need to populate that column with 5 spaces.
I am using trim function for all the columns
Suggestions required please
Thanks in advance
Saik
Re: null handling
Hi Saik,rafidwh wrote:Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns
I am using trim function for all the columns
Suggestions required please
Thanks in advance
Saik
trim of all space value is null, right. You can change the column defintion of the columns you want to .[allow spaces to not null
For the data type of varchar it is ok to give single space, in case of char datatype it is must to have 5 spaces. If it is decimal , it would be considired as null. There may be some other reasons, like unique constrains voilation for record reject.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
May I know the purpose of using trim for all the column.
Yes, you try the option suggested by prabu. By using trim, you are removing the left out space, where the target expects at least 5 space in the char. If trim is part of your logic, you can use handle_null.
Yes, you try the option suggested by prabu. By using trim, you are removing the left out space, where the target expects at least 5 space in the char. If trim is part of your logic, you can use handle_null.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Re: null handling
Set Null Field Value property to '' (notice no space in b/w quotes).rafidwh wrote:Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns it is taking as null values and is rejected in the table.
Do I need to populate those columns with spaces by handlling null or is there any other options.
If my column length is 5 should I need to populate that column with 5 spaces.
I am using trim function for all the columns
Suggestions required please
Thanks in advance
Saik
and add the following environment variable
APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL set it to 1.
You should be good to go. You dont have to check for spaces again. But if you are performing any transformations on these fields make sure you perform ISNULL check before.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Hi,
I am trying to load an oracle table and have issues loading null values into NON-Nullable fields.
I did use in the transformer preceding the oracle stage
1) If IsNull(Src.Col) then " " else Src.col
(Also tried with If IsNull(Src.Col) then ' ' else Src.col)
2) Set APT_ORACLE_PRESERVE_BLANKS to TRUE
But it still rejects with sql code 1400.
When I write the same stream to a flat file it shows space in the field values.
The target contains Char, Decimal, VarChar and integer columns, is there any other parameter that we need to set before trying to load space into Oracle non-nullable fields?
Thanks,
I am trying to load an oracle table and have issues loading null values into NON-Nullable fields.
I did use in the transformer preceding the oracle stage
1) If IsNull(Src.Col) then " " else Src.col
(Also tried with If IsNull(Src.Col) then ' ' else Src.col)
2) Set APT_ORACLE_PRESERVE_BLANKS to TRUE
But it still rejects with sql code 1400.
When I write the same stream to a flat file it shows space in the field values.
The target contains Char, Decimal, VarChar and integer columns, is there any other parameter that we need to set before trying to load space into Oracle non-nullable fields?
Thanks,
Thanks,
Vinay
Vinay
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Finally i think i have figured out the issue, the char column comming in is not being identified as Null, I used the following
IsNull(Trim(Src.Col))
and I made a simple job that checks and writes the nulls and not nulls based on Src.Col to 2 different streams and all the rows go down the NOT NULLS path !
When I open the file to read the column it is blank .
Any ideas on how to trim non-readable data?
IsNull(Trim(Src.Col))
and I made a simple job that checks and writes the nulls and not nulls based on Src.Col to 2 different streams and all the rows go down the NOT NULLS path !
When I open the file to read the column it is blank .
Any ideas on how to trim non-readable data?
Thanks,
Vinay
Vinay
vinaymanchinila wrote:Finally i think i have figured out the issue, the char column comming in is not being identified as Null, I used the following
IsNull(Trim(Src.Col))
and I made a simple job that checks and writes the nulls and not nulls based on Src.Col to 2 different streams and all the rows go down the NOT NULLS path !
When I open the file to read the column it is blank .
Any ideas on how to trim non-readable data?
Can you try
If Trim(Col)="" then "default value" else if IsNull(Col) tehn "default value" else Col...
Let me know if this works out for u...
"Attitude always and almost determines the altitude of your Life"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Wouldn't it be easier just to set the Null Field Value property in the Sequential File stage, for example to "<NULL>", so that spaces continue to be treated as spaces?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.