Can I use Insert or update statement in User defined sql?

[ICE]

Dear All,

May I know whether I can use the Insert or update function in user defined sql?
If I can use, may I know the syntax???


Thanks in advance,

[ray.wurlod]

Specify the "Update action" property as "insert or update" or "update or insert". Look at the View SQL tab. Then revert to user defined SQL and put in the two statements.

Otherwise, include a flag ('U' or 'I') and call a stored procedure that implements your user-defined SQL statements.

[ICE]

Dear Ray,

Yes.I have been used this way. But the job has aborted.
Is there any special character need to give between 2 statements???


Thanks for your advice.

Specify the "Update action" property as "insert or update" or "update or insert". Look at the View SQL tab. Then revert to user defined SQL and put in the two statements.

Otherwise, include a fla ...

[rafik2k]

Can you post the sql you are using insert or update?

Also post log error message for that

[ray.wurlod]

They are stored in separate fields in the record in DS_JOBOBJECTS, so my best guess would be a line terminator character or field mark. Or it just may not be possible. Any reason for preferring user-defined over generated SQL?

[ICE]

dEAR rafik2k,

Below u can find my sql and error log.

1.SQL that I used in the user-defined sql.
INSERT INTO LIB1.destinationtable(ID, TR, DAT, VEN, DPT, CT, ST, CTN, CN, YY, MM, WK, DD, RET, CST, RAT, SDEP, COMM, QTY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WITH NONE;
UPDATE LIB1.destinationtable SET ID = ?, TR = ?, DAT = ?, VEN = ?, DPT = ?, CT = ?, ST = ?, CTN = ?, CN = ?, YY = ?, MM = ?, WK = ?, DD = ?, RET = ?, CST = ?, RAT = ?, SDEP = ?, COMM = ?, QTY = ? WHERE (SID = ? AND STR = ? AND DAT = ? AND VEN = ? AND DPT = ? AND CT = ? AND CTN = ? AND SDEP = ?) WITH NONE;

2.Error log that I got when I run.

UpdateJob1.CTransformerStage3.Lnk_To_Destinatintable: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO LIB11.Destinatintable(ID, SS, DAT, VEN, DPT, CT, ST, CTN, CN, YY, MM, WK, DD, RET, CST, RAT, SDEP, COMM, QTY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WITH NONE;
UPDATE LIB11.Destinatintable SET ID = ?, TR = ?, DAT = ?, VEN = ?, DPT = ?, CT = ?, ST = ?, CTN = ?, CN = ?, YY = ?, MM = ?, WK = ?, DD = ?, RET = ?, CST = ?, RAT = ?, SDEP = ?, COMM = ?, QTY = ? WHERE (SID = ? AND STR = ? AND DAT = ? AND VEN = ? AND DPT = ? AND CT = ? AND CTN = ? AND SDEP = ?) WITH NONE
SQLSTATE=S1009, DBMS.CODE=0
[Ardent][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Invalid argument value

ID = 9
TR = 5
DAT = 70514
VEN = 7315
DPT = 24
CT = 1
ST = "CC"
CTN = 353
CN = 0
YY = 7
MM = 5
WK = 20
DD = 1
RET = 7662.94
CST = 0.00
RAT = 4.00
SDEP = 8
COMM = 306.51
QTY = 2

Is there any idea u have???

Thanks so much,

Can you post the sql you are using insert or update?

Also post log error message for that

[chulett]

You've got what, 19 parameter markers in the Insert statement and something like 27 in the Update. It doesn't work like that. They are positional markers - how many columns did you define in the stage?

You are also re-using field names in the update sql, are you including the column names twice in the stage, once with old values and once with new values? Sure doesn't look like it.

Why would you not use two distinct links for this?

[ICE]

Dear Chulett,

I am not very clear that what you mean.
May I know what is the parameter markers?
Both sql have 19 parameters that is generated by DSJ. Before I use the user defined sql, first I choose the insert or update option to get the generated sql like what Ray said. Then I change to user defined sql.So both sql is created by DS job.
I have one question here. May I know can I use the two sql in user defined sql?


Thanks in advance,
ice

You've got what, 19 parameter markers in the Insert statement and something like 27 in the Update. It doesn't work like that. They are positional markers - how many columns did yo ...

[chulett]

Parameter markers are the question marks in your SQL, bind points waiting for column values to be supplied at runtime. Positional meaning that the position of the ? in the sql is mapped to the position of each column defined in the stage. For example, when it sees 19 of them, it sends the value in Column 1 to the 1st question mark, the value in column 2 to the 2nd one, etc. It got to the 20th '?' in your update sql and didn't have a column #20 to match to it. Kaboom - Invalid argument value.

"May I know can I use the two sql in user defined sql?"

Simplest answer - don't. I personally believe it to be an extremely poor choice of design even if it 'works'. It's a crutch - throw it away and learn to walk on your own. Use two links. Know when you need to do inserts and when you need to do updates. Don't cross the streams Dr Venkman!

[ICE]

Dear CHulett,

Now I am using the way what you suggested
Thanks, Thanks a lot.....
I am very appreciated your help.

:D

Parameter markers are the question marks in your SQL, bind points waiting for column values to be supplied at runtime. Positional meaning that the position of the ? in the sql is mapped to the position of each column defined in the stage. For example, when it sees 19 of them, it sends the value in Column 1 to the 1st question mark, the value in column 2 to the 2nd one, etc. It got to the 20th '?' in your update sql and didn't have a column #20 to match to it. Kaboom - Invalid argument value.

"May I know can I use the two sql in user defined sql?"

Simplest answer - don't. I personally believe it to be an extremely poor choice of design even if it 'works'. It's a crutch - throw it away and learn to walk on your own. Use two links. Know when you need to do inserts and when you need to do updates. Don't cross the streams Dr Venkman!