Merge wont run with dbc.SysExecSQL

Database

Merge wont run with dbc.SysExecSQL

MERGE INTO  D_CUL_CR_VW.V_OFFER_M  AS TGT USING (SELECT OFFER_ID,OFFER_ROW_SEQ_ID,AUTHORIZATION_ID,XREF_AGREEMENT_ID,ORIGINAL_AUTHORIZATION_ID,BASIC_DATA_INCOMPLETE_FLAG,NINE_DIGIT_KEYLINE_ID,FIFTEEN_DIGIT_KEYLINE_ID,DNIS_CDE,INTERACTION_ADVISOR_SCORE_DES,PACKAGE_CDE,OFFER_DES,IN_HOME_DTE,REPLY_BY_DTE,PROMO_ID,SOURCE_LAST_MODIFIED_TSP,LAST_MODIFIED_TSP,VALID_FROM_TSP,VALID_TO_TSP,VALID_FROM_DTE,VALID_TO_DTE,ETL_STAGE_TAG_ID,ETL_CR_TAG_ID,ETL_ERROR_IND,UNIQUE_ROW_ID,OPEN_ROW_IND,SSR_CODE_NUM,OFFER_IN_HOME_START_DTE,OFFER_IN_HOME_END_DTE,SOURCE_SYSTEM_CREATED_TSP,AUTH_ID_YR_NUM,AUTH_ID_PRODUCT_CDE,OFFER_STAGE_NUM FROM D_CUL_WORK_TB.T_OFFER_M2 ) AS  STG ON (TGT.OFFER_ROW_SEQ_ID = STG.OFFER_ROW_SEQ_ID AND TGT.OFFER_ID = STG.OFFER_ID  AND  STG.OFFER_STAGE_NUM = TGT.OFFER_STAGE_NUM AND STG.AUTH_ID_PRODUCT_CDE = TGT.AUTH_ID_PRODUCT_CDE AND STG.AUTH_ID_YR_NUM = TGT.AUTH_ID_YR_NUM )  WHEN MATCHED THEN UPDATE SET OPEN_ROW_IND = STG.OPEN_ROW_IND, VALID_TO_DTE = STG.VALID_TO_DTE ,VALID_TO_TSP = STG.VALID_TO_TSP  WHEN NOT MATCHED THEN INSERT ( STG.OFFER_ID ,STG.OFFER_ROW_SEQ_ID ,STG.AUTHORIZATION_ID ,STG.XREF_AGREEMENT_ID ,STG.ORIGINAL_AUTHORIZATION_ID ,STG.BASIC_DATA_INCOMPLETE_FLAG ,STG.NINE_DIGIT_KEYLINE_ID ,STG.FIFTEEN_DIGIT_KEYLINE_ID ,STG.DNIS_CDE ,STG.INTERACTION_ADVISOR_SCORE_DES ,STG.PACKAGE_CDE ,STG.OFFER_DES ,STG.IN_HOME_DTE ,STG.REPLY_BY_DTE ,STG.PROMO_ID ,STG.SOURCE_LAST_MODIFIED_TSP ,STG.LAST_MODIFIED_TSP ,STG.VALID_FROM_TSP ,STG.VALID_TO_TSP ,STG.VALID_FROM_DTE ,STG.VALID_TO_DTE ,STG.ETL_STAGE_TAG_ID ,STG.ETL_CR_TAG_ID ,STG.ETL_ERROR_IND ,STG.UNIQUE_ROW_ID ,STG.OPEN_ROW_IND ,STG.SSR_CODE_NUM ,STG.OFFER_IN_HOME_START_DTE ,STG.OFFER_IN_HOME_END_DTE ,STG.SOURCE_SYSTEM_CREATED_TSP ,STG.AUTH_ID_YR_NUM ,STG.AUTH_ID_PRODUCT_CDE ,STG.OFFER_STAGE_NUM  ); 

I have a merge statement, that is dynamically created by a stored proc.  I have the merge statement being spit out into an output variable so I can see what it built.

I can take this statement, and execute it just fine in a SQL window in teradata studio.

However, this same mergestament is giving an error with the SysExecSQl call.

Error State: 30758

Error Code: 5758

Things I am confident in:

I am using the correct user, in other cases this statement when built differently works fine in sysexecsql

All permissions are fine

I am in fact spitting out the same variable the call is using, so they are the same.

What I am testing is when I have multiple partition keys, which is new to use, this one specific table has 3.  Here is the merge, which executes fine in an sql scrapbook window:

2 REPLIES

Re: Merge wont run with dbc.SysExecSQL

MERGE INTO  D_CUL_CR_VW.V_OFFER_M  AS TGT USING (SELECT OFFER_ID,OFFER_ROW_SEQ_ID,AUTHORIZATION_ID,XREF_AGREEMENT_ID,ORIGINAL_AUTHORIZATION_ID,BASIC_DATA_INCOMPLETE_FLAG,NINE_DIGIT_KEYLINE_ID,FIFTEEN_DIGIT_KEYLINE_ID,DNIS_CDE,INTERACTION_ADVISOR_SCORE_DES,PACKAGE_CDE,OFFER_DES,IN_HOME_DTE,REPLY_BY_DTE,PROMO_ID,SOURCE_LAST_MODIFIED_TSP,LAST_MODIFIED_TSP,VALID_FROM_TSP,VALID_TO_TSP,VALID_FROM_DTE,VALID_TO_DTE,ETL_STAGE_TAG_ID,ETL_CR_TAG_ID,ETL_ERROR_IND,UNIQUE_ROW_ID,OPEN_ROW_IND,SSR_CODE_NUM,OFFER_IN_HOME_START_DTE,OFFER_IN_HOME_END_DTE,SOURCE_SYSTEM_CREATED_TSP,AUTH_ID_YR_NUM,AUTH_ID_PRODUCT_CDE,OFFER_STAGE_NUM FROM D_CUL_WORK_TB.T_OFFER_M2 ) AS  STG ON (TGT.OFFER_ROW_SEQ_ID = STG.OFFER_ROW_SEQ_ID AND TGT.OFFER_ID = STG.OFFER_ID  AND  STG.OFFER_STAGE_NUM = TGT.OFFER_STAGE_NUM AND STG.AUTH_ID_PRODUCT_CDE = TGT.AUTH_ID_PRODUCT_CDE AND STG.AUTH_ID_YR_NUM = TGT.AUTH_ID_YR_NUM )  WHEN MATCHED THEN UPDATE SET OPEN_ROW_IND = STG.OPEN_ROW_IND, VALID_TO_DTE = STG.VALID_TO_DTE ,VALID_TO_TSP = STG.VALID_TO_TSP  WHEN NOT MATCHED THEN INSERT ( STG.OFFER_ID ,STG.OFFER_ROW_SEQ_ID ,STG.AUTHORIZATION_ID ,STG.XREF_AGREEMENT_ID ,STG.ORIGINAL_AUTHORIZATION_ID ,STG.BASIC_DATA_INCOMPLETE_FLAG ,STG.NINE_DIGIT_KEYLINE_ID ,STG.FIFTEEN_DIGIT_KEYLINE_ID ,STG.DNIS_CDE ,STG.INTERACTION_ADVISOR_SCORE_DES ,STG.PACKAGE_CDE ,STG.OFFER_DES ,STG.IN_HOME_DTE ,STG.REPLY_BY_DTE ,STG.PROMO_ID ,STG.SOURCE_LAST_MODIFIED_TSP ,STG.LAST_MODIFIED_TSP ,STG.VALID_FROM_TSP ,STG.VALID_TO_TSP ,STG.VALID_FROM_DTE ,STG.VALID_TO_DTE ,STG.ETL_STAGE_TAG_ID ,STG.ETL_CR_TAG_ID ,STG.ETL_ERROR_IND ,STG.UNIQUE_ROW_ID ,STG.OPEN_ROW_IND ,STG.SSR_CODE_NUM ,STG.OFFER_IN_HOME_START_DTE ,STG.OFFER_IN_HOME_END_DTE ,STG.SOURCE_SYSTEM_CREATED_TSP ,STG.AUTH_ID_YR_NUM ,STG.AUTH_ID_PRODUCT_CDE ,STG.OFFER_STAGE_NUM  ); 

Re: Merge wont run with dbc.SysExecSQL

MERGE INTO  D_CUL_CR_VW.V_OFFER_M  AS TGT USING (SELECT OFFER_ID,OFFER_ROW_SEQ_ID,AUTHORIZATION_ID,XREF_AGREEMENT_ID,ORIGINAL_AUTHORIZATION_ID,BASIC_DATA_INCOMPLETE_FLAG,NINE_DIGIT_KEYLINE_ID,FIFTEEN_DIGIT_KEYLINE_ID,DNIS_CDE,INTERACTION_ADVISOR_SCORE_DES,PACKAGE_CDE,OFFER_DES,IN_HOME_DTE,REPLY_BY_DTE,PROMO_ID,SOURCE_LAST_MODIFIED_TSP,LAST_MODIFIED_TSP,VALID_FROM_TSP,VALID_TO_TSP,VALID_FROM_DTE,VALID_TO_DTE,ETL_STAGE_TAG_ID,ETL_CR_TAG_ID,ETL_ERROR_IND,UNIQUE_ROW_ID,OPEN_ROW_IND,SSR_CODE_NUM,OFFER_IN_HOME_START_DTE,OFFER_IN_HOME_END_DTE,SOURCE_SYSTEM_CREATED_TSP,AUTH_ID_YR_NUM,AUTH_ID_PRODUCT_CDE,OFFER_STAGE_NUM FROM D_CUL_WORK_TB.T_OFFER_M2 ) AS  STG ON (TGT.OFFER_ROW_SEQ_ID = STG.OFFER_ROW_SEQ_ID AND TGT.OFFER_ID = STG.OFFER_ID  AND  STG.OFFER_STAGE_NUM = TGT.OFFER_STAGE_NUM AND STG.AUTH_ID_PRODUCT_CDE = TGT.AUTH_ID_PRODUCT_CDE AND STG.AUTH_ID_YR_NUM = TGT.AUTH_ID_YR_NUM )  WHEN MATCHED THEN UPDATE SET OPEN_ROW_IND = STG.OPEN_ROW_IND, VALID_TO_DTE = STG.VALID_TO_DTE ,VALID_TO_TSP = STG.VALID_TO_TSP  WHEN NOT MATCHED THEN INSERT ( STG.OFFER_ID ,STG.OFFER_ROW_SEQ_ID ,STG.AUTHORIZATION_ID ,STG.XREF_AGREEMENT_ID ,STG.ORIGINAL_AUTHORIZATION_ID ,STG.BASIC_DATA_INCOMPLETE_FLAG ,STG.NINE_DIGIT_KEYLINE_ID ,STG.FIFTEEN_DIGIT_KEYLINE_ID ,STG.DNIS_CDE ,STG.INTERACTION_ADVISOR_SCORE_DES ,STG.PACKAGE_CDE ,STG.OFFER_DES ,STG.IN_HOME_DTE ,STG.REPLY_BY_DTE ,STG.PROMO_ID ,STG.SOURCE_LAST_MODIFIED_TSP ,STG.LAST_MODIFIED_TSP ,STG.VALID_FROM_TSP ,STG.VALID_TO_TSP ,STG.VALID_FROM_DTE ,STG.VALID_TO_DTE ,STG.ETL_STAGE_TAG_ID ,STG.ETL_CR_TAG_ID ,STG.ETL_ERROR_IND ,STG.UNIQUE_ROW_ID ,STG.OPEN_ROW_IND ,STG.SSR_CODE_NUM ,STG.OFFER_IN_HOME_START_DTE ,STG.OFFER_IN_HOME_END_DTE ,STG.SOURCE_SYSTEM_CREATED_TSP ,STG.AUTH_ID_YR_NUM ,STG.AUTH_ID_PRODUCT_CDE ,STG.OFFER_STAGE_NUM  );