Handle Timestamp null when inserting in OCI
Moderators: chulett, rschirm, roy
Handle Timestamp null when inserting in OCI
Hi all,
Got a problem with Timestamp.
I read data from a dataset then join with datasets then go throught a transformer and then make an upsert into an Oracle table.
I have some columns that i have to check in the transformer.
If data_from_the_beginning is NULL
then
take_the_data_from_joiner (timestamp)
OR
stringtotimestamp(take_the_data_from_the_beginning) (varchar)
The problem is that sometimes i don't have data in these both columns and the OCI drop the rows telling me that take_the_data_from_joiner is NULL.
I want to insert into Oracle table even if there's no value and it seems that OCI stage want to convert null to timestamp even if there's no data.
How can i insert without losing my rows and put null into these timestamp columns if the columns are null ??
Got a problem with Timestamp.
I read data from a dataset then join with datasets then go throught a transformer and then make an upsert into an Oracle table.
I have some columns that i have to check in the transformer.
If data_from_the_beginning is NULL
then
take_the_data_from_joiner (timestamp)
OR
stringtotimestamp(take_the_data_from_the_beginning) (varchar)
The problem is that sometimes i don't have data in these both columns and the OCI drop the rows telling me that take_the_data_from_joiner is NULL.
I want to insert into Oracle table even if there's no value and it seems that OCI stage want to convert null to timestamp even if there's no data.
How can i insert without losing my rows and put null into these timestamp columns if the columns are null ??
[quote="DSguru2B"]Does your table accept NULL for the timestamp field? If you get rid of the database stage and load it to a flat file with the timestamp field set to null, can you load the data into a file?[/quote]
if i put a flat file with link reject i can see these rows and there's nothing in these columns.
The column in the Oracle table is nullable.
if i put a flat file with link reject i can see these rows and there's nothing in these columns.
The column in the Oracle table is nullable.
The field is nullable everywhere in the stages and in Oracle too.
I have to check if fields are null or not and sometimes they are but the transformer reject the rows because the field is null.
I'm going crazy with this problem. Server Edition is easier to work with.
the message is that the column is null and there's a conversion problem.
I do not do conversion type for this column because it's already timestamp from a dataset. so the rows are dropped !!
I have to check if fields are null or not and sometimes they are but the transformer reject the rows because the field is null.
I'm going crazy with this problem. Server Edition is easier to work with.
the message is that the column is null and there's a conversion problem.
I do not do conversion type for this column because it's already timestamp from a dataset. so the rows are dropped !!
DSguru2B wrote:Thats odd. If you have it defined as nullable all accross and it still rejects it then that means something is missing. Double, triple check in all the input/output tabs of all the stages.
Re: Handle Timestamp null when inserting in OCI
Resolved !
My problem was => too many stages and a trsfmr at the end that gone crazy.
Explication:
My job was
DS => colImport => 2 JOINs => TSFMR => OCI
The conversions type gone crazy in transformer so i create 3 jobs
One for the Joins
One for the tranformations
One for the Insert in Oracle Table
It work fine and last job deals fine with with null timestamps.
Thank you guys !!
My problem was => too many stages and a trsfmr at the end that gone crazy.
Explication:
My job was
DS => colImport => 2 JOINs => TSFMR => OCI
The conversions type gone crazy in transformer so i create 3 jobs
One for the Joins
One for the tranformations
One for the Insert in Oracle Table
It work fine and last job deals fine with with null timestamps.
Thank you guys !!
CLOPES wrote:Hi all,
Got a problem with Timestamp.
I read data from a dataset then join with datasets then go throught a transformer and then make an upsert into an Oracle table.
I have some columns that i have to check in the transformer.
If data_from_the_beginning is NULL
then
take_the_data_from_joiner (timestamp)
OR
stringtotimestamp(take_the_data_from_the_beginning) (varchar)
The problem is that sometimes i don't have data in these both columns and the OCI drop the rows telling me that take_the_data_from_joiner is NULL.
I want to insert into Oracle table even if there's no value and it seems that OCI stage want to convert null to timestamp even if there's no data.
How can i insert without losing my rows and put null into these timestamp columns if the columns are null ??
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That was a delightful piece of "Franglais"; thinking in French (or maybe Spanish, but that would make it "Spanglish") but communicating in English.CLOPES wrote:conversions type gone crazy in transformer
Aucune critique.
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.