00-000-00 Date problem
Posted: Thu May 15, 2008 12:14 pm
Hi,
I have a job with just two ORA stages and all i am trying to read data from a source table and do an upsert on the target table. There are no other stages inbetween these two stages.
A select query runs in first ORA stage and it has some date columns. In the target stage, i have a user-defined Upsert, with an Update Before Insert option. The Update statement just updates the effective end dates and current indicator and update date in case the record already exists, else it is supposed to execute the insert (just like a typical SCD2)
What is happening is, i have source data with null date columns, but when it does the upsert to this other table, it is inserting a '00-000-00' instead of null, which i cant see unless i run a query
select end_date, to_char(end_date,'yyyy') from table_name
it shows me a null in the end date column, but a '0000' in the to_char return value.
Has anyone encountered this problem before or can suggest where/how this funny default value is getting inserted. I cant even begin to analyze if its oracle/DS problem, do i have to do additional null handling on dates even if date is a nullable column on target?
Thanks in advance
I have a job with just two ORA stages and all i am trying to read data from a source table and do an upsert on the target table. There are no other stages inbetween these two stages.
A select query runs in first ORA stage and it has some date columns. In the target stage, i have a user-defined Upsert, with an Update Before Insert option. The Update statement just updates the effective end dates and current indicator and update date in case the record already exists, else it is supposed to execute the insert (just like a typical SCD2)
What is happening is, i have source data with null date columns, but when it does the upsert to this other table, it is inserting a '00-000-00' instead of null, which i cant see unless i run a query
select end_date, to_char(end_date,'yyyy') from table_name
it shows me a null in the end date column, but a '0000' in the to_char return value.
Has anyone encountered this problem before or can suggest where/how this funny default value is getting inserted. I cant even begin to analyze if its oracle/DS problem, do i have to do additional null handling on dates even if date is a nullable column on target?
Thanks in advance