Upsert Failing
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
Upsert Failing
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!!!
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!!!
Mac4rfree
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
If i am giving this Custom Query, it is working fine.
But if i remove D_CRT from the Update, it is giving the below fatal errror.
The query i am using when i am getting Error is
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'))
Code: Select all
ORA-01036: illegal variable name/number
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'))
Mac4rfree
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
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.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 ...
Can you please guide me as how to achieve that?
Mac4rfree
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
First of all thanks for opening your post.chulett wrote:I already did and since it was only four words I've opened my 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.
Mac4rfree