Skip to Content
0

Hi, I have a problem executing a procedure in sap HANA

Sep 24, 2017 at 09:54 AM

87

avatar image
Former Member

Error:

"Could not execute 'CALL "_SYS_BIC"."EVALUADOR_LOCAL/EVAL_PROMOS_L2"' in 806 ms 15 µs . SAP DBTech JDBC: [9]: index out of bounds: "_SYS_BIC"."EVALUADOR_LOCAL/EVAL_PROMOS_L2": line 21 col 2 (at pos 828): [9] (range 3): index out of bounds: param index exceeded the param size 0, but 1"

Code:

BEGIN DECLARE COTA1, COTA2 DATE; DECLARE EXIST_TABLE INTEGER; SELECT MIN(PER_START_DT) INTO COTA1 FROM "EVALUADOR_LOCAL"."PERIODOS"; SELECT MAX(PER_END_DT) INTO COTA2 FROM "EVALUADOR_LOCAL"."PERIODOS"; EXIST_TABLE = 0; select count(*) into EXIST_TABLE from TABLES where schema_name ='EVALUADOR_LOCAL' and table_name='KPI_PERIODOS'; IF (EXIST_TABLE > 0) THEN DROP TABLE EVALUADOR_LOCAL.KPI_PERIODOS; END IF; CREATE COLUMN TABLE EVALUADOR_LOCAL.KPI_PERIODOS AS( SELECT PER.CADENA, PER.LOCATION_ID, PER.N_PROMO, PER.PERIODO, SUM(DI.MONTO_NETO) AS VENTA_NETA, COUNT(DISTINCT(CASE WHEN DI.PARTY_ID != '-2' THEN DI.PARTY_ID END)) AS CLIENTES_FID, SUM(DI.ITEM_QTY_UMB) AS UNIDADES, COUNT(DISTINCT(DI.LOCATION_ID||DI.POS_REGISTER_ID||DI.TRAN_START_DT||DI.TRAN_START_TM||DI.TRAN_NUM)) AS TRX, SUM(CASE WHEN DI.MARCA_TMAS = '1' THEN DI.MONTO_NETO ELSE 0 END)*1.0 AS VENTA_NETA_TC, CASE WHEN (SUM(DI.MONTO_NETO)) != 0 THEN (SUM(CASE WHEN DI.MARCA_TMAS = '1' THEN DI.MONTO_NETO ELSE 0 END)*1.0)*1.0/(SUM(DI.MONTO_NETO)) END AS PART_VENTA_TC, SUM(DI.WEIGHTED_AVERAGE_COST_AMT) AS COSTO, COUNT(DISTINCT( CASE WHEN DI.CHAIN_CD IN ('JU','JS') AND SVJ.SEGMENT_VALUE_CD = 'P' THEN DI.PARTY_ID WHEN DI.CHAIN_CD IN ('SA') AND SVS.SEGMENT_VALUE_CD = 'P' THEN DI.PARTY_ID END)) AS CLIENTES_FID_PREF FROM CL_SUPER_DIM.DIM_DIA_ID_ITEM AS DI JOIN "EVALUADOR_LOCAL"."PERIODOS" AS PER ON DI.LOCATION_ID LIKE PER.CADENA||'%' AND DI.LOCATION_ID = PER.LOCATION_ID AND DI.TRAN_START_DT BETWEEN PER.PER_START_DT AND PER.PER_END_DT JOIN "EVALUADOR_LOCAL"."cpintom_SMK_PROMOS_SKU" AS SKU ON PER.N_PROMO = SKU.N_PROMO AND PER.CADENA = SKU.CADENA AND DI.ITEM_ID = RIGHT('000000000000000000'||SKU.SKU, 18) JOIN "EVALUADOR_LOCAL"."IQPROD_JPCORDOVA_MESES" AS M ON DI.CODMES = M.CODMES LEFT JOIN CL_SUPER_DIM.SEG_CRM_MONTHLY_SEGMENT_VALUE_JUMBO AS SVJ ON DI.PARTY_ID = SVJ.PARTY_ID AND SVJ.CODMES = M.M_1 LEFT JOIN CL_SUPER_DIM.SEG_CRM_MONTHLY_SEGMENT_VALUE_SISA AS SVS ON DI.PARTY_ID = SVS.PARTY_ID AND SVS.CODMES = M.M_1 WHERE DI.TRAN_START_DT BETWEEN COTA1 AND COTA2 AND DI.LOCATION_ID IN ( SELECT LOCATION_ID FROM EVALUADOR_LOCAL.SMK_LOCALES_BLANCA ) AND DI.LOCATION_ID NOT IN ( SELECT LOCATION_ID FROM EVALUADOR_LOCAL.SMK_LOCALES_NEGRA ) GROUP BY PER.CADENA, PER.LOCATION_ID, PER.N_PROMO, PER.PERIODO); END;
10 |10000 characters needed characters left characters exceeded

Can you paste a more detailed trace from the indexserver.trc file (not the alert file).

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers