Upsert Failing

mac4rfree85 · Post by **mac4rfree85** » Mon Aug 22, 2011 3:51 am

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!!!

priyadarshikunal · Post by **priyadarshikunal** » Mon Aug 22, 2011 3:55 am

can you post the error you are getting??

mac4rfree85 · Post by **mac4rfree85** » Mon Aug 22, 2011 4:17 am

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'))

ray.wurlod · Post by **ray.wurlod** » Mon Aug 22, 2011 6:03 am

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.

chulett · Post by **chulett** » Mon Aug 22, 2011 6:24 am

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.

mac4rfree85 · Post by **mac4rfree85** » Mon Aug 22, 2011 6:46 am

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?

chulett · Post by **chulett** » Mon Aug 22, 2011 6:53 am

I already did and since it was only four words I've opened my post.

mac4rfree85 · Post by **mac4rfree85** » Tue Aug 23, 2011 5:14 am

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.

chulett · Post by **chulett** » Tue Aug 23, 2011 7:05 am

Yes, that's exactly what I meant.