Stored Procedure error

General
Enthusiast

Stored Procedure error

Hi Folks,

I am new to Tera data. I am writing a stored procedure to generate an alert in alert table if certain condition is met. I am getting the below error when I am trying t execute the same.

5531: Named list is not supported for arguments of a procedure.

Apprecaite your help if you can guide me to correct SP.

Thanks

CREATE PROCEDURE SP_rmp_STP_ECL (vPERIODID INT)

BEGIN

/*INSERT INTO rmp_STP_INPUTDATA_SS

SELECT * FROM RAW_GPS_ARC_MESSAGES WHERE PERIODID=*/

DELETE FROM rmp_ECL_SUM WHERE PERIODID=vPERIODID;

DELETE FROM rmp_VIOL_ECL_WTP WHERE ALERTPERIODID=vPERIODID;

DELETE FROM rmp_VIOL_DET_ECL_WTP WHERE ALERTDETAILPERIODID=vPERIODID;

--INSERTING VALUES INTO SUMMARY TABLE---

INSERT INTO rmp_ECL_SUM (SCENARIOID,SUMID,PERIODID,ORGID,ORGBANK,ORGBANKCOUNTRY,ORGCOUNTRY,BENID,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,DEBITCREDIT,TRANDATE,TOTALTRANAMT,

                                                        TOTALDEBITAMT,TOTALCREDITAMT,NUMOFTRANS,SEGMENT,CREATEDON,CREATEDBY,RUNDATE)

SELECT

        'ECL' AS SCENARIOID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING),PERIODID,ORGID,ORGBANK,ORGBANKCOUNTRY,ORGCOUNTRY,BENID,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,DEBITCREDIT,TRANDATE,

        SUM(CASE WHEN AMOUNT IS NULL OR AMOUNT='' THEN 0 ELSE AMOUNT END)  TOTALTRANAMT,

        SUM(CASE  WHEN DEBITCREDIT = 'DEBIT' THEN AMOUNT  ELSE   0  END) TOTALDEBITAMT,

        SUM(CASE  WHEN DEBITCREDIT = 'CREDIT' THEN AMOUNT  ELSE   0  END)TOTALCREDITAMT,

        COUNT(*)  TOTALTRANCOUNT, SEGMENT,CURRENT_TIMESTAMP,'SP_rmp_STP_CB_ECL',

        case

WHEN TRIM(vPERIODID) = 132 THEN CAST('12/16/2012 15:22:09' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

WHEN TRIM(vPERIODID) = 133 THEN CAST('01/19/2013 12:49:43' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

WHEN TRIM(vPERIODID) = 134 THEN CAST('02/15/2013 01:37:15' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

WHEN TRIM(vPERIODID) = 135 THEN CAST('03/08/2013 22:48:59' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

WHEN TRIM(vPERIODID) = 136 THEN CAST('03/21/2013 13:19:01' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

WHEN TRIM(vPERIODID) = 137 THEN CAST('04/06/2013 02:17:26' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

else cast('01/01/0001 00:00:00' as  TIMESTAMP(0)  format 'mm/dd/yyyybhh:mi:ss')

end as WTP_RUN_DATE

FROM( rmp_ST_WTP_INPUTDATA_SS A

LEFT JOIN

                                        (SELECT PARTYID FROM rmp_EXCLUDE_PARTIES_LIST_SS

                                                WHERE         case

                                                                                                WHEN TRIM(vPERIODID) = 132 THEN CAST('12/16/2012 15:22:09' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                WHEN TRIM(vPERIODID) = 133 THEN CAST('01/19/2013 12:49:43' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                WHEN TRIM(vPERIODID) = 134 THEN CAST('02/15/2013 01:37:15' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                WHEN TRIM(vPERIODID) = 135 THEN CAST('03/08/2013 22:48:59' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                WHEN TRIM(vPERIODID) = 136 THEN CAST('03/21/2013 13:19:01' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                WHEN TRIM(vPERIODID) = 137 THEN CAST('04/06/2013 02:17:26' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                else cast('01/01/0001 00:00:00' as  TIMESTAMP(0)  format 'mm/dd/yyyybhh:mi:ss')

                                                                                                end BETWEEN STARTDATE AND ENDDATE ) C ON A.ORGID=C.PARTYID  OR  A.BENID=C.PARTYID)

                LEFT JOIN

                                        (SELECT ORGPARTYID,BENPARTYID

                                                FROM rmp_EXCLUDE_PARTY_PAIR_SS

                                                        WHERE         case

                                                                                                        WHEN TRIM(vPERIODID) = 132 THEN CAST('12/16/2012 15:22:09' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        WHEN TRIM(vPERIODID) = 133 THEN CAST('01/19/2013 12:49:43' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        WHEN TRIM(vPERIODID) = 134 THEN CAST('02/15/2013 01:37:15' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        WHEN TRIM(vPERIODID) = 135 THEN CAST('03/08/2013 22:48:59' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        WHEN TRIM(vPERIODID) = 136 THEN CAST('03/21/2013 13:19:01' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        WHEN TRIM(vPERIODID) = 137 THEN CAST('04/06/2013 02:17:26' AS  TIMESTAMP(0) FORMAT  'MM/DD/YYYYBHH:MI:SS')

                                                                                                        else cast('01/01/0001 00:00:00' as  TIMESTAMP(0)  format 'mm/dd/yyyybhh:mi:ss')

                                                                                                        end BETWEEN STARTDATE AND ENDDATE ) D ON ((A.ORGID=D.ORGPARTYID AND A.BENID=D.BENPARTYID) OR (A.ORGID=D.BENPARTYID AND A.BENID=D.ORGPARTYID))

WHERE

AMOUNT > (SELECT PARAMETERVALUE FROM rmp_THRESHOLDS_SS  WHERE  PARAMETERNAME='MINVALUE' and RULENAME ='ECL')

AND C.PARTYID IS NULL

AND D.ORGPARTYID IS NULL

GROUP BY  SCENARIOID,PERIODID,ORGID,ORGBANK,ORGBANKCOUNTRY,ORGCOUNTRY,BENID,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,DEBITCREDIT,TRANDATE, SEGMENT,CURRENT_TIMESTAMP,'SP_rmp_STP_CB_ECL',WTP_RUN_DATE;

--INSERTING VALUES INTO ALERTS TABLE--

INSERT INTO rmp_VIOL_ECL_WTP (ALERTID,ALERTSCENARIOID,ALERTPERIODID,ALERTORGID,ALERTBENID,ALERTTRANAMT,ALERTDEBITCREDIT,ALERTCREDITAMOUNT, ALERTDEBITAMOUNT,ALERTCREATEDON,ALERTCREATEDBY,ALERTSEGMENT)

SELECT SUM(1) OVER (ROWS UNBOUNDED PRECEDING),A.SCENARIOID,A.PERIODID,A.ORGID, A.BENID, A.TOTALTRANAMT,A.DEBITCREDIT,A.TOTALCREDITAMT,A.TOTALDEBITAMT,

CURRENT_TIMESTAMP,'SP_rmp_STP_CB_ECL',A.SEGMENT

From  rmp_ECL_SUM A

INNER JOIN

(SELECT PARAMETERVALUE, SEGMENT,RULENAME FROM  rmp_THRESHOLDS_SS  WHERE  RULENAME='ECL') E ON A.SEGMENT=E.SEGMENT AND A.SCENARIOID=E.RULENAME

WHERE( A.DEBITCREDIT = ANY  ('DEBIT' OR ' CREDIT')) AND

(A.TOTALDEBITAMT  >= E.PARAMETERVALUE OR

A.TOTALCREDITAMT >= E.PARAMETERVALUE)

AND

(A.BENCOUNTRY <> A.BENBANKCOUNTRY OR A.ORGCOUNTRY <> A.ORGBANKCOUNTRY);

END;
3 REPLIES
Enthusiast

Re: Stored Procedure error

When I am inserting call sp_STP_ECL(NULL)  with null in brackets it is executing the SP but no alerts are generated.

Appreciate if you can guide me.

Teradata Employee

Re: Stored Procedure error

Try following:

CREATE PROCEDURE SP_rmp_STP_ECL (IN vPERIODID INT)

- Use :vPERIODID .... instead of vPERIODID

- Confirm your INSERTS run fine outside of SP

Teradata Employee

Re: Stored Procedure error

Am getting the same error for external stored procedures.  My XSP seems to work find if I give it a constant input example "CALL sitxspj_coladd(1, 4 ,?);"

But it does not work if a give a column as input instead of the constants. I wanted to know if that is even possible?