All Forums General
chachandra 7 posts Joined 02/14
11 Apr 2014
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,ORGBANKCOUNTR Y,ORGCOUNTRY,BENID,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,D EBITCREDIT,TRANDATE,TOTALTRANAMT,
                                                        TOTALDEBITAMT,TOTALCREDITAMT,NUMOFTRANS,SEGMENT,CREATE DON,CREATEDBY,RUNDATE)
SELECT
        'ECL' AS SCENARIOID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING) ,PERIODID,ORGID,ORGBANK,ORGBANKCOUNTRY,ORGCOUNTRY,BENI D,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,DEBITCREDIT,TRANDA TE,
        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,ORGCO UNTRY,BENID,BENBANK,BENCOUNTRY,BENBANKCOUNTRY,DEBITCRE DIT,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,ALER TBENID,ALERTTRANAMT,ALERTDEBITCREDIT,ALERTCREDITAMOUNT , ALERTDEBITAMOUNT,ALERTCREATEDON,ALERTCREATEDBY,ALERTSE GMENT)
SELECT SUM(1) OVER (ROWS UNBOUNDED PRECEDING),A.SCENARIOID,A.PERIODID,A.ORGID, A.BENID, A.TOTALTRANAMT,A.DEBITCREDIT,A.TOTALCREDITAMT,A.TOTALD EBITAMT,
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;

 

chachandra 7 posts Joined 02/14
11 Apr 2014

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.

Adeel Chaudhry 773 posts Joined 04/08
12 Apr 2014

Try following:
 
 
- CREATE PROCEDURE SP_rmp_STP_ECL (IN vPERIODID INT)
- Use :vPERIODID .... instead of vPERIODID
- Confirm your INSERTS run fine outside of SP

-- If you are stuck at something .... consider it an opportunity to think anew.

AS186162 1 post Joined 05/15
23 Jun 2015

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?

You must sign in to leave a comment.