Page 1 of 1
Upsert Failing
Posted: Mon Aug 22, 2011 3:51 am
by mac4rfree85
Hi Guys,
I have 5 columns in a table. Out of them, 2 cols are CreateDate and UpdateDate.
The data which will be inserted will be upsert.
In the update statement, when i use only the UpdateDate column, the job is aborting. But when i am using InsertDate column along with the UpdateDate column, the job is running fine.
I have not changed anything else in the query.
I am not able to understand this. Can somebody shed some light on this.
Cheers!!!
Posted: Mon Aug 22, 2011 3:55 am
by priyadarshikunal
can you post the error you are getting??
Posted: Mon Aug 22, 2011 4:17 am
by mac4rfree85
If i am giving this Custom Query, it is working fine.
Code: Select all
UPDATE EDW.REL_CATALOG_HEADER SET col1=:2,D_CRT=TO_TIMESTAMP(:3, 'YYYY-MM-DD HH24:MI:SS'),D_UPD=TO_TIMESTAMP(:4, 'YYYY-MM-DD HH24:MI:SS') WHERE KEYCOL=:1;
INSERT INTO EDW.REL_CATALOG_HEADER (keycol,col1,D_CRT,D_UPD) VALUES (:1,:2,TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'))
But if i remove D_CRT from the Update, it is giving the below fatal errror.
Code: Select all
ORA-01036: illegal variable name/number
The query i am using when i am getting Error is
Code: Select all
UPDATE EDW.REL_CATALOG_HEADER SET col1=:2,D_UPD=TO_TIMESTAMP(:4, 'YYYY-MM-DD HH24:MI:SS') WHERE KEYCOL=:1;
INSERT INTO EDW.REL_CATALOG_HEADER (keycol,col1,D_CRT,D_UPD) VALUES (:1,:2,TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'))
Posted: Mon Aug 22, 2011 6:03 am
by ray.wurlod
My guess is that D_CRT is not nullable and has no default value, so that when an upsert statement (particularly an insert statement) affects it, null is attempted to be used as the default value, which is illegal.
Posted: Mon Aug 22, 2011 6:24 am
by chulett
Nope, it's simply the fact that all colums in the stage must be bound into the query. Each must mention all four, so if you don't want to update all fields you'll need to pre-qualify your actions and use two separate links.
Posted: Mon Aug 22, 2011 6:46 am
by mac4rfree85
chulett wrote:Nope, it's simply the fact that all colums in the stage must be bound into the query. Each must mention all four, so if you don't want to update all fields you'll need to pre-qualify your actio ...
Yeah chulett i agree, but i want the update to be running only for those records which already have the KeyCol and not for all the records which includes the New Records also.
Can you please guide me as how to achieve that?
Posted: Mon Aug 22, 2011 6:53 am
by chulett
I already did and since it was only four words I've opened my post.
Posted: Tue Aug 23, 2011 5:14 am
by mac4rfree85
chulett wrote:I already did and since it was only four words I've opened my post.
First of all thanks for opening your post.
I did not get pre qualify part of it. You mean to say do a lookup with the target table and create two links. one for update and one for insert?
Could you please clarify..
Thanks once again.
Posted: Tue Aug 23, 2011 7:05 am
by chulett
Yes, that's exactly what I meant.