SAP HANA General error;328 invalid name of function or procedure:

[former_member488878]

Hi there,

I have script a script in POSTTRANSCATIONNOTICE that breaks an item on sales invoice according to its quantity. ie. if an item has 10 quantity in sales invoice it will insert ten rows for this item in a udo. Following is the script.

IF :object_type = '13' AND (:transaction_type = 'A' OR :transaction_type = 'U')
THEN
DECLARE I nvarchar(50);
DECLARE D nvarchar(50);
DECLARE Q INT;
DECLARE CNT INT;
DECLARE OL INT;
DECLARE OCNT INT;
DECLARE XN NVARCHAR(50);
DECLARE XN1 NVARCHAR(50);
DECLARE MX INT;
DECLARE AQTY INT;
DECLARE BS INT;
CNT = 1;
OCNT = 0;
XN = '';
XN1 = '';
MX = 0;
BS = 0;
SELECT MAX(Y.RN) INTO OL FROM 
(
SELECT ROW_NUMBER( ) OVER( ORDER BY X."ItemCode" ) AS "RN" , X."ItemCode", X."Dscription", X."Quantity" FROM 
(SELECT A."ItemCode", A."Dscription", A."Quantity" FROM INV1 AS A WHERE A."DocEntry" = :list_of_cols_val_tab_del GROUP BY A."ItemCode", A."Dscription", A."Quantity") AS X ) AS Y;
WHILE (OCNT <= OL)
DO
OCNT = OCNT +1;
SELECT Y."ItemCode", Y."Dscription", (CASE WHEN Y."U_brk" = 'N' THEN 1 ELSE Y."Quantity" END), (CASE WHEN Y."U_brk" = 'N' THEN Y."Quantity" ELSE 1 END), Y."BaseEntry" INTO I,D,Q,AQTY, BS
FROM 
(
  SELECT ROW_NUMBER( ) OVER( ORDER BY X."ItemCode" ) AS "RN" , X."ItemCode", X."Dscription", X."Quantity", X."U_brk", X."BaseEntry" FROM
  (
    SELECT A."ItemCode", A."Dscription", A."Quantity", A."U_brk", A."BaseEntry" FROM INV1 AS A WHERE A."DocEntry" = :list_of_cols_val_tab_del GROUP BY A."ItemCode", A."Dscription", A."Quantity",A."U_brk", A."BaseEntry"  ) AS X 
) AS Y 
WHERE Y.RN = OCNT;
WHILE (CNT <= Q)
DO
  SELECT (IFNULL(MAX(A."U_GeneratedNo"), 0)+1) INTO MX FROM "@OINB" AS A; 
   SELECT IFNULL(V."DistNumber", 'Not Assigned'), IFNULL(V."MnfSerial" , 'Not Assigned') INTO XN, XN1 FROM 
(SELECT  ROW_NUMBER( ) OVER(PARTITION BY T0."ItemCode" ORDER BY T1."DistNumber"||T1."MnfSerial" ) AS XN, T0."ItemCode", T1."DistNumber", T1."MnfSerial" FROM SRI1 AS T0 LEFT JOIN OSRN AS T1 ON T0."ItemCode" = T1."ItemCode" AND  T0."SysSerial"=T1."SysNumber" WHERE T0."BaseType" = 15 AND T0."BaseEntry" = BS) AS V WHERE V.XN = CNT AND V."ItemCode" = I ; 
INSERT INTO "@OINB" ( "Code", "U_ItemCode", "U_ItemName", "U_Quantity", "U_EngineNo", "U_ChasisNo", "U_DocNum", "U_GeneratedNo", "U_DocEntry") VALUES (MX,I, D, AQTY, XN, XN1, :list_of_cols_val_tab_del, MX, :list_of_cols_val_tab_del);
  CNT = CNT+1;
  XN = '';
  XN1 = '';
END WHILE;
  I = '' ;
  D = '' ;
  Q=0;
  CNT = 1;
  AQTY = 0;
  BS = 0;
END WHILE;
END IF;

When I add a sales invoice it does the required work but also gives following red line error :

[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] General error;328 invalid name of function or procedure: tmp_qry_sp_b6dffe72-d1b8-42aa-93f0-3bc5b27af243: line 1 col 26  '' (DLN1) (at pos 25)

Is there some function that is not allowed in HANA. its says col 26 and the function at col 26 is OVER(ORDER BY "ItemCode")? Is that not allowed in HANA.

Any help is appreciated..

Regards!

Norman

[milkzo]

Hi,

I have below same error when saving as draft from delivery document in SAP B1.

[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;328 invalid name of function or procedure: tmp_qry_sp_9f73c161-4916-4768-8eb4-b1cb60fc8928: line 1 col 41 (at pos 40).

All functions and procedures defined on the database are desactivated?

Have you found the cause of this issue?

Thanks for the help

Regards,

Gueye

[akshinde]

Hi , error seems to be different, not what you are thinking, order by is allowed in windows function row_number(), you are ordering by T1.DistNumber, T1.MnfSerial by concating these columns in your windows function row_number(), which I believe is syntactically wrong.

Can you not avoid while loop, we can generate 10 rows for header having 10 quantity using different mechanism.

[former_member488878]

Thanks for the reply,

If it is syntactically wrong then should not it give me the error at time of executing the Procedure query in HANA Studio? And if it is wrong then it should not do the required work, but it does the work alongside this error.

Can you kindly give me any idea about what can I use other than While loop.?