Empty spaces in OCI Target table

[kris007]

Hi all,

I have created a simple job that reads data from the source table through ODBC stage and writes it to an Oracle target table( I am using a OCI stage) through a transformer. The only transformation I do in the transformer is to find out if there are any empty spaces in the source table and write them as NULL into my target table. The job finishes succesfully, however, I still can see some empty spaces in my target table instead of NULL values. Did anyone come across this kind of issue? Any sort of help would be great.

Additional Info:(thought this mite help)
The data in the source table consists of rows containing data, empty spaces and NULL values. Now, when I pull this data into an ODBC stage, the NULL values come in as empty spaces. So, in my ODBC stage I have rows containing data and empty spaces. But in Target table, only those rows are written as NULL which are empty in source table, and the rows which contain null values in the source are written to target as empty spaces. Please let me know if I am not clear so that I could explain it again in a more detailed way.

[I_Server_Whale]

Hi Kris,

You can write the target column derivation as:

Code: Select all

If

Len(Trim(Outlink.Col)) = 0 or Outlink.Col = ''

Then

''

Else 

OutLink.Col = InLink.Col

Code: Select all

Note: '' is for NULL, meaning there is no space between the quotes.

Let me know if it worked.

Thanks,
Naveen.

[kumar_s]

Hi Kris,

You can write the target column derivation as:

Code: Select all

If

Len(Trim(Outlink.Col)) = 0 or Outlink.Col = ''

Then

''

Else 

OutLink.Col = InLink.Col

Code: Select all

Note: '' is for NULL, meaning there is no space between the quotes.

Let me know if it worked.

Thanks,
Naveen.

I afraid NULL is quite different from '' Empty string.
@NULL can be used rather.

[kris007]

Hi Naveen,

Thanks for that.
It worked fine for columns whose datatype is varchar2 but didnt work for column whose datatype is number. Am I missing something out over here.
please let me know. And as Kumar mentioned..NULL is different from empty string. But I need NULL at my ouput..so I used 'NULL' instead of ''.

Thanks guys
-Kris

[I_Server_Whale]

Hi kumar,

Thanks for correcting me. I should have double-checked.
Yes. Empty String <> NULL


In Fact the code should be:

Code: Select all

If 

Len(Trim(Outlink.Col)) = 0 or Outlink.Col = @NULL 

Then 

@NULL 

Else 

OutLink.Col = InLink.Col 

Sorry! Kris. I hope you corrected it.

Many Thanks,
Naveen.

[MTA]

Naveen is there a difference between Outlink.Col = '' and Outlink.Col = ""
please note the difference in cotes

[kumar_s]

Naveen is there a difference between Outlink.Col = '' and Outlink.Col = ""
please note the difference in cotes

[kris007]

I have corrected it Naveen. Thanks. But as I mentioned, My problem still continues with the rows for the column whose datatype is number. Is there a way out for that. I seem to miss it. I am still waiting....

Kris

[kumar_s]

Hi Kris,
Datastage converts Null to 0s. [(0x00) as Integer 0]

[I_Server_Whale]

MTA,


There is no difference between '' and "". Both are the same.
But it is quotes and not cotes.

thanks,
Naveen.

[kris007]

I got my issue solved. I declared the datatype as a number in the target table and was trying to write "NULL" if its an empty string. And hence the job was getting aborted because the table is reading "NULL" as an invalid number. I can't believe I missed that. Thanks once again guys.
:D
Kris