DSXchange

No,
Sorry,It was pasting error

Thanks

Apologies on my part, I re-read your previous post. Insert is working but update is not huh..
Have you tried generated sql query as I asked you before.
The error maybe due to the fact that you are passing less number of columns in update as compared to the number specified in the column's tab. Try adding all the columns and see if that works.

Thanks DSGuru2B,
I tried as you said and found that Actully Insert Giving me Fatal:

INSERT INTO Table_name
( SUB_NM, G_SK, EFF_DT,EXP_DT, C_CD, A_SK)

VALUES
(
ORCHESTRATE.SUB_NM,
ORCHESTRATE.G_SK ,
CURRENT DATE,
'9999-12-31',
'C',
ORCHESTRATE.A_SK
)

What is wrong with this ? All columns are there in Column Tab.

Thanks

swades wrote: Actully Insert Giving me Fatal:

What is the Fatal error???

I think it doesn't like the string '9999-12-31' - what is the column data type for that value? Perhaps you need to cast this string to a date or timestamp...

I am sorry
Actully I posted in earlier post
it is :

DataDirect][ODBC lib] Program type out of range

You think its the CURRENT DATE that might be the problem? What database are you connecting to. Get the current date within the transformer and pass it to the column "EFF_DT"

Thanks

what is the column data type for that value?

It is Date.

What database are you connecting to

It is DB2

As I posted earlier: In Update SQL I am using data from Tranformer for Updating.
BUT
to Insert I am using this SQL so I am Hard coding it

Thanks

Try "DATE('9999-12-31')"

Append to above Post :

Or any other option to perform same logic I mean to perform Update and then insert.

Did you try Inserting from command line? Does that work? Have you tried just insert and just update?

Thanks

Have you tried just insert and just update?

Yes both are working.

Thanks

Did you try ArndW's suggestion?
Alternatively, do all the hardcoding in the transformer and dont hardcode anything inside the sql.
Are you getting any sql codes along with your error message, including the warning messages?

Thanks

Did you try ArndW's suggestion?

Yes

I want to Update and Insert both at one pass ( I mean 1st do some update to Target Table then Insert new Record)

Are you getting any sql codes along with your error message, including the warning messages?

No

Thanks a lot

ArndW wrote:What are your column definitions? At least one of these is incorrect for DataStage.

Post the reply for ArndW's question. There is little we can do sitting on the other side of your screen. Try making the date column as varchar and see if that helps.