stored procedures

Database
Enthusiast

stored procedures

I have this procedure 

REPLACE PROCEDURE DB_SP.SP_LOAD ( IN PRCS INTEGER )
BEGIN

CREATE MULTISET VOLATILE TABLE UNDERWRITER1 AS
(
SELECT o. PRTY_DESC , o.INTRNL_ORG_NUM, pa.AGMT_ID
FROM TABA o, TABB pa
WHERE o.INTRNL_ORG_PRTY_ID=pa.PRTY_ID
AND pa.PRTY_AGMT_ROLE_CD= 'CMP'
AND DATE BETWEEN pa.EDW_STRT_DTTM AND pa.EDW_END_DTTM
AND DATE BETWEEN o.EDW_STRT_DTTM AND o.EDW_END_DTTM

)
WITH DATA PRIMARY INDEX (AGMT_ID)
ON COMMIT PRESERVE ROWS;


CREATE MULTISET VOLATILE TABLE UNDERWRITER2 AS
(
SELECT o. PRTY_DESC , o.INTRNL_ORG_NUM, pa.AGMT_ID
FROM TABA o, TABB pa
WHERE o.INTRNL_ORG_PRTY_ID=pa.PRTY_ID
AND pa.PRTY_AGMT_ROLE_CD= 'CMP'
AND DATE BETWEEN pa.EDW_STRT_DTTM AND pa.EDW_END_DTTM
AND DATE BETWEEN o.EDW_STRT_DTTM AND o.EDW_END_DTTM

)
WITH DATA PRIMARY INDEX (AGMT_ID)
ON COMMIT PRESERVE ROWS;


END;

 

if i call this procedure directly in teradata sql assistant using

 

call DB_SP.SP_LOAD ( 999), it is getting executed without any issues

 

If i try to call it through informatica (i.e using a SQL trnsformation and pass call DB_SP.SP_LOAD ( 999),), i get Only an ET or null statement is legal after a DDL Statement. Not sure why?

 

The ODBC connection which informatica uses is not set to ANSI which is same as my teradata sql assistant. why am i getting error only in informatica and not through teradata sql assistant

1 REPLY
Teradata Employee

Re: stored procedures

Either a "BT;" is being inserted prior to the CALL, or the CALL is being submitted as part of a multi-statement request.

Perhaps an INFA specialist can tell you how to avoid that.