I have a macro in which I have two statements. One is the SP call and the other one is a SELECT statement. The body of both SP and macro is given below:
REPLACE PROCEDURE WAQ.DATA_VALIDATION(
, pPROVIDED_VAL VARCHAR(500)
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = ' provided value is ';
REPLACE MACRO WAQ.create_load_script (Process_Name (VARCHAR(500)), Collect_Stats (VARCHAR(500)), Verification_Flag (VARCHAR(500)) )
CALL WAQ.DATA_VALIDATION('Collect_Stats', :Collect_Stats)
I am calling macro using the following statement in SQLA:
EXEC WAQ.create_load_script('WAQ', 'P', '1');
When I execute the macro using above statement I get an error "Teredata Error 5497: CALL cannot be submitted in multi-statement request"
However, If I keep only one statement in macro (by either removing SP or SELECT call) it works fine without any error. I have tried different positions of semi colon in the macro including
but of no use and still getting the same error from both SQLA and BTEQ.
Any help would be highly appreciated.
The body of a macro is executed as a single request.
If the macro contains more than one statement, it is a multi-statement request.