00-000-00 Date problem

[jreddy]

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

[kumar_s]

Where do you have the query running?

[keshav0307]

what is the key columns for update?
do you have unique constraint on the key columns?

[jreddy]

kumar_s,

I didnt get your question, but if you are asking about DB, its ORACLE 10g

[jreddy]

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.

[ray.wurlod]

I think it's entirely reasonably that this error is being generated, because 00-000-00 simply is NOT a date.

[jreddy]

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.

[chulett]

What happens if you dump that column? I'd be curious what's actually in there rather than what to_char pulls out.

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

That will give us the data type, length and hexidecimal contents of the field.

[jreddy]

craig,

This is what comes up when i run the dump

Typ=12 Len=7: 0,0,0,0,0,0,0

Thanks

[ray.wurlod]

Type 12 in DataStage is VarChar. Looks like you need an IsValid() test somewhere in your mix.

[chulett]

That's an Oracle 'type 12' which means DATE. If the field was null the function would return the word 'NULL' rather than those hex zeroes. I'm not really sure what to make of this one.