0

I have an SP in Netezza to do UPSERTS between two tables by using a composite key .

It Compiles Correctly but then throws this error :

ERROR: unexpected transaction command in exec_stmt_execsql() - not allowed in stored procedure

DECLARE USERINPUT ALIAS FOR $1; ...... BEGIN DELETE FROM MAIN_TABLE WHERE TMS_NETWORK_ID||TMS_PROGRAM_ID IN (SELECT TMS_NETWORK_ID||TMS_PROGRAM_ID FROM T_F_SPLIT_PROG_SCHEDULE_STG) AND RUN_ID = USERINPUT ; INSERT INTO MAIN_TABLE SELECT FROM STAGING_TABLE WHERE RUN_ID = USERINPUT AND DEL_FLAG = 0;

..... END;

It seems i m trying something that isnt allowed in Netezza SP's , but i dont know what is causing the error. Please Help...??

Arpan J
  • 11
  • 1

1 Answers1

0

I saw your SP and noted few points -

1) You are deleting rows from your main table and then inserting from raw/staging table to main table, this is not advisable way from documents perspective because it causes a lot more processes in memory to delete and can cause "serialization error" if any parallel thread will try to insert/update any record in main table.

2) I found some errors in your query -

DELETE FROM MAIN_TABLE WHERE TMS_NETWORK_ID||TMS_PROGRAM_ID IN (SELECT TMS_NETWORK_ID||TMS_PROGRAM_ID FROM T_F_SPLIT_PROG_SCHEDULE_STG) AND RUN_ID = USERINPUT

I guess you are using composite keys but the way you are selecting and trying to delete is wrong.

Varun Bajaj
  • 1,033
  • 8
  • 16