00-000-00 Date problem
Moderators: chulett, rschirm, roy
00-000-00 Date problem
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
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
keshav,
I do have a key column that i am using for update and i do have unique constraint on the table as well for that column, but again the problem is not with the key columns, its the date columns that are nullable columns and just other non-key attributes on the table.
Instead of dumping a null in the date column when reading a null value from source query, it is setting this default value.
I do have a key column that i am using for update and i do have unique constraint on the table as well for that column, but again the problem is not with the key columns, its the date columns that are nullable columns and just other non-key attributes on the table.
Instead of dumping a null in the date column when reading a null value from source query, it is setting this default value.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ray,
When the job runs, it doesnt throw any error at all. Its when i query the tables later that i realize that it has this wierd value.
Let me explain with an example to further elaborate on my problem. I may not have explained the problem right in my initial post.
Source record:
----------------------
item_id: 1
item_seq_no: 2
item_pur_dt: <null>
quantity: 10
Requirement of job: if a record already exists with item_seq_no:2 for item_id:1, then it is supposed to update record with rec_end_dt and if it doesnt exist, then insert a record with those details as above.
In my example above, source record has null for the date right, but when i run this job, there are no errors, The record gets inserted because it does not exist. But the value of the column item_pur_dt is not set to null, it is set to '00-000-00'. and this happens only for certain records randomly, they are different with each run.
When i look at the target table records in TOAD, it seems like its null, but when i query as below i see the '00-000-00' in the last column, its pbly in the format of oracle's default dd-mon-yyyy
select item_pur_dt, quantity, to_char(item_pur_dt)
from <target_table_name>
where item_id = 1
and item_seq_no = 2
I run the same query against source table name, in the last column i see null as expected.
When the job runs, it doesnt throw any error at all. Its when i query the tables later that i realize that it has this wierd value.
Let me explain with an example to further elaborate on my problem. I may not have explained the problem right in my initial post.
Source record:
----------------------
item_id: 1
item_seq_no: 2
item_pur_dt: <null>
quantity: 10
Requirement of job: if a record already exists with item_seq_no:2 for item_id:1, then it is supposed to update record with rec_end_dt and if it doesnt exist, then insert a record with those details as above.
In my example above, source record has null for the date right, but when i run this job, there are no errors, The record gets inserted because it does not exist. But the value of the column item_pur_dt is not set to null, it is set to '00-000-00'. and this happens only for certain records randomly, they are different with each run.
When i look at the target table records in TOAD, it seems like its null, but when i query as below i see the '00-000-00' in the last column, its pbly in the format of oracle's default dd-mon-yyyy
select item_pur_dt, quantity, to_char(item_pur_dt)
from <target_table_name>
where item_id = 1
and item_seq_no = 2
I run the same query against source table name, in the last column i see null as expected.
What happens if you dump that column? I'd be curious what's actually in there rather than what to_char pulls out.
That will give us the data type, length and hexidecimal contents of the field.
Code: Select all
select item_pur_dt, quantity, dump(item_pur_dt,16)
from <target_table_name>
where item_id = 1
and item_seq_no = 2
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: