Problem activating scripted calculation view

[Former Member]

I'm trying to create a SQL scripted calculation view, but I keep getting an activation error.  Can anyone please help?

I'm using the following script:

/********* Begin Procedure Script ************/

BEGIN

selectOpen=

SELECT 'OPEN' AS "SALES_STAGE"

,"SAPECP"."VBAK"."VBELN" AS "ORDER_NO" --order no

,'' AS "DELIVERY_NO"

,'' AS "BILLING_NO"

,"SAPECP"."VBAK"."AUART" AS "DOC_TYPE" --doc type

,"SAPECP"."VBAK"."VDATU" AS "SHIP_DATE"--shipping date

,"SAPECP"."VBAK"."KUNNR" AS "CUSTOMER" --cust no

,"SAPECP"."VBAK"."WAERK" --currency

,"SAPECP"."VBAP"."POSNR" --line item no

,"SAPECP"."VBAP"."MATNR" --mat no

,"SAPECP"."VBAP"."ARKTX" --mat desc

,"SAPECP"."VBAP"."MEINS" --ordered qty uom

,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."KWMENG" AS "ORDER_QTY" --order qty

,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."BRGEW" AS "BRGEW" --gross weight

,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."NTGEW" AS "NTGEW" --net weight

,"SAPECP"."VBAP"."GEWEI" -- gross/net weight uom

,CASE WHEN "SAPECP"."VBAK"."WAERK" = 'JPY' THEN 100 ELSE 1 END * "SAPECP"."VBAP"."NETPR" AS "NETPR" --unit price

,"SAPECP"."VBAP"."KPEIN" --unit price per

,"SAPECP"."VBAP"."KMEIN" --price uom

,CASE WHEN "SAPECP"."VBAK"."WAERK" = 'JPY' THEN 100 ELSE 1 END * CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."NETWR" AS "NETWR" --total amount, SAP handles JPY differently

FROM "SAPECP"."VBAK"

JOIN "SAPECP"."VBAP" ON "SAPECP"."VBAK"."VBELN"="SAPECP"."VBAP"."VBELN" AND "SAPECP"."VBAP"."NETWR" <>0

LEFT OUTER JOIN "SAPECP"."VBFA" ON "SAPECP"."VBAK"."VBELN" = "SAPECP"."VBFA"."VBELV" /*AND "SAPECP"."VBAP"."POSNR"="SAPECP"."VBFA"."POSNN" */ AND "SAPECP"."VBFA"."MANDT"=420 AND "SAPECP"."VBFA"."VBTYP_N" IN ('J','T','O','S')--"SAPECP"."VBFA"."VBTYP_N"='J'  --,'O','S'

WHERE "SAPECP"."VBAK"."MANDT" = 420

AND "SAPECP"."VBAP"."MANDT" = 420

AND "SAPECP"."VBFA"."VBELV" IS NULL

AND "SAPECP"."VBAK"."AUART" NOT IN ('ZCQ','KM','B1','G2','L2')

AND "SAPECP"."VBAP"."ABGRU" = ''  --reason for rejection, signifies cancellation at line level versus "SAPECP"."VBAK"."AUBRU"

AND "SAPECP"."VBAK"."KUNNR" NOT LIKE 'IC%'

--AND "SAPECP"."VBAK"."VBELN" = 20

GROUP BY

"SAPECP"."VBAK"."VBELN"  --order no

,"SAPECP"."VBAK"."VDATU" --shipping date

,"SAPECP"."VBAK"."KUNNR" --cust no

,"SAPECP"."VBAK"."WAERK" --currency

,"SAPECP"."VBAP"."POSNR" --line item no

,"SAPECP"."VBAP"."MATNR" --mat no

,"SAPECP"."VBAP"."ARKTX" --mat desc

,"SAPECP"."VBAP"."MEINS" --ordered qty uom

,"SAPECP"."VBAP"."KWMENG" --order qty

,"SAPECP"."VBAP"."BRGEW" --gross weight

,"SAPECP"."VBAP"."NTGEW" --net weight

,"SAPECP"."VBAP"."GEWEI" -- gross/net weight uom

,"SAPECP"."VBAP"."NETPR" --unit price

,"SAPECP"."VBAP"."KPEIN" --unit price per

,"SAPECP"."VBAP"."KMEIN" --price uom

,"SAPECP"."VBAP"."NETWR" --total amount

,"SAPECP"."VBAK"."AUART" --doc type

END

/********* End Procedure Script ************/

Here's the error message:

Message :

  Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "END": line 58 col 1 (at pos 3004)nSet Schema DDL statement: set schema "SAPECP"nType DDL: create type "_SYS_BIC"."sap.reportviews/Z_SALES_PIPELINE_SCRIPTED_CAV/proc/tabletype/VAR_OUT" as table ("SALES_STAGE" NVARCHAR(10), "ORDER_NO" NVARCHAR(10), "DELIVERY_NO" NVARCHAR(10), "BILLING_NO" NVARCHAR(10), "DOC_TYPE" NVARCHAR(4), "SHIP_DATE" NVARCHAR(8), "CUSTOMER" NVARCHAR(10), "WAERK" NVARCHAR(5), "POSNR" NVARCHAR(6), "MATNR" NVARCHAR(18), "ARKTX" NVARCHAR(40), "MEINS" NVARCHAR(3), "ORDER_QTY" DECIMAL(15,3), "BRGEW" DECIMAL(15,3), "NTGEW" DECIMAL(15,3), "GEWEI" NVARCHAR(3), "NETPR" DECIMAL(11,2), "KPEIN" DECIMAL(5,0), "KMEIN" NVARCHAR(3), "NETWR" DECIMAL(15,2))nProcedure DDL: create procedure "_SYS_BIC"."sap.reportviews/Z_SALES_PIPELINE_SCRIPTED_CAV/proc" ( OUT var_out "_SYS_BIC"."sap.reportviews/Z_SALES_PIPELINE_SCRIPTED_CAV/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN nnselectOpen=nSELECT 'OPEN' AS "SALES_STAGE"n,"SAPECP"."VBAK"."VBELN" AS "ORDER_NO" --order non,'' AS "DELIVERY_NO"n,'' AS "BILLING_NO"n,"SAPECP"."VBAK"."AUART" AS "DOC_TYPE" --doc typen,"SAPECP"."VBAK"."VDATU" AS "SHIP_DATE"--shipping daten,"SAPECP"."VBAK"."KUNNR" AS "CUSTOMER" --cust non,"SAPECP"."VBAK"."WAERK" --currencyn,"SAPECP"."VBAP"."POSNR" --line item non,"SAPECP"."VBAP"."MATNR" --mat non,"SAPECP"."VBAP"."ARKTX" --mat descn,"SAPECP"."VBAP"."MEINS" --ordered qty uomn,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."KWMENG" AS "ORDER_QTY" --order qtyn,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."BRGEW" AS "BRGEW" --gross weightn,CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."NTGEW" AS "NTGEW" --net weightn,"SAPECP"."VBAP"."GEWEI" -- gross/net weight uomn,CASE WHEN "SAPECP"."VBAK"."WAERK" = 'JPY' THEN 100 ELSE 1 END * "SAPECP"."VBAP"."NETPR" AS "NETPR" --unit price n,"SAPECP"."VBAP"."KPEIN" --unit price pern,"SAPECP"."VBAP"."KMEIN" --price uomn,CASE WHEN "SAPECP"."VBAK"."WAERK" = 'JPY' THEN 100 ELSE 1 END * CASE WHEN "SAPECP"."VBAK"."AUART" = 'RE' THEN -1 ELSE 1 END * "SAPECP"."VBAP"."NETWR" AS "NETWR" --total amount, SAP handles JPY differently nFROM "SAPECP"."VBAK" nJOIN "SAPECP"."VBAP" ON "SAPECP"."VBAK"."VBELN"="SAPECP"."VBAP"."VBELN" AND "SAPECP"."VBAP"."NETWR" <>0nLEFT OUTER JOIN "SAPECP"."VBFA" ON "SAPECP"."VBAK"."VBELN" = "SAPECP"."VBFA"."VBELV" /*AND "SAPECP"."VBAP"."POSNR"="SAPECP"."VBFA"."POSNN" */ AND "SAPECP"."VBFA"."MANDT"=420 AND "SAPECP"."VBFA"."VBTYP_N" IN ('J','T','O','S')--"SAPECP"."VBFA"."VBTYP_N"='J'  --,'O','S'nWHERE "SAPECP"."VBAK"."MANDT" = 420nAND "SAPECP"."VBAP"."MANDT" = 420nAND "SAPECP"."VBFA"."VBELV" IS NULLnAND "SAPECP"."VBAK"."AUART" NOT IN ('ZCQ','KM','B1','G2','L2')nAND "SAPECP"."VBAP"."ABGRU" = ''  --reason for rejection, signifies cancellation at line level versus "SAPECP"."VBAK"."AUBRU"nAND "SAPECP"."VBAK"."KUNNR" NOT LIKE 'IC%'n--AND "SAPECP"."VBAK"."VBELN" = 20nnGROUP BYn"SAPECP"."VBAK"."VBELN"  --order non,"SAPECP"."VBAK"."VDATU" --shipping daten,"SAPECP"."VBAK"."KUNNR" --cust non,"SAPECP"."VBAK"."WAERK" --currencyn,"SAPECP"."VBAP"."POSNR" --line item non,"SAPECP"."VBAP"."MATNR" --mat non,"SAPECP"."VBAP"."ARKTX" --mat descn,"SAPECP"."VBAP"."MEINS" --ordered qty uomn,"SAPECP"."VBAP"."KWMENG" --order qtyn,"SAPECP"."VBAP"."BRGEW" --gross weightn,"SAPECP"."VBAP"."NTGEW" --net weightn,"SAPECP"."VBAP"."GEWEI" -- gross/net weight uomn,"SAPECP"."VBAP"."NETPR" --unit price n,"SAPECP"."VBAP"."KPEIN" --unit price pern,"SAPECP"."VBAP"."KMEIN" --price uomn,"SAPECP"."VBAP"."NETWR" --total amountn,"SAPECP"."VBAK"."AUART" --doc typennnnENDn/********* End Procedure Script ************/n

[pfefferf · Accepted solution]

Hello Robert,

seems that you didn't finish your statement with a ";".

In the SQL console it is not necessary in case you have just one statement.

Best Regards,

Florian

[Former Member · Accepted solution]

Thanks.  I actually figured it out about 15 minutes after I posted , but thanks for the information anyway.

Rob

[Former Member]

I forgot to mention that the SQL works fine in an SQL console in SAP HANA Studio...