All Forums Database
WAQ 158 posts Joined 02/10
05 Aug 2016
Teredata Error 5497: CALL cannot be submitted in multi-statement request

Hi,
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(
	pCOLN_NAME VARCHAR(500)
	, pPROVIDED_VAL VARCHAR(500)
)

BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		RESIGNAL;
	END;
	SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = ' provided value is ';

END;
REPLACE MACRO  WAQ.create_load_script (Process_Name (VARCHAR(500)), Collect_Stats (VARCHAR(500)), Verification_Flag (VARCHAR(500)) )
AS (
	CALL WAQ.DATA_VALIDATION('Collect_Stats', :Collect_Stats)
	;
	SELECT CURRENT_TIMESTAMP
	;
)
;

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

CALL
;SELECT;

but of no use and still getting the same error from both SQLA and BTEQ.
Any help would be highly appreciated.

Fred 1096 posts Joined 08/04
05 Aug 2016

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.

WAQ 158 posts Joined 02/10
06 Aug 2016

Thanks Fred for your response. So Can't we do it the way I am trying to do?

Macros can have more than one SQL statement but can't we add a SP call with the SQL or any other statement in the macro?

You must sign in to leave a comment.