Skip to Content

SQL Querying HANA Calculation View with input parameters

Hi experts,

I am need of some help in understanding the reason for my hana graphical view is not pulling of any data.

My SQL procedure is as below.

CREATE PROCEDURE "CONFIG"."PROC_INVENTORYVALUE"() AS

BEGIN

DECLARE V_MONTH NVARCHAR(6);

DECLARE CURSOR tt_PLANTS FOR SELECT "0SOURSYSTEM","ZPLANT","ZREGION" FROM "_SYS_BIC"."CVD_PLANT" WHERE "ZREGION"!='';

V_MONTH = '201811';

FOR CUR_ROW AS tt_PLANTS DO

SELECT "SourceSystem", "Region", "Plant", "WarehouseNumber", "StorageLocation", "SalesOrderNumber", "SalesOrderStatus" FROM "_SYS_BIC"."CVU_REP_IVR_REPORT" (PLACEHOLDER."$SOURCESYSTEM_1$"=>:CUR_ROW."0SOURSYSTEM", PLACEHOLDER."$CAL_MONTH$"=>:V_MONTH, PLACEHOLDER."$REGION_1$"=>:CUR_ROW."ZREGION", PLACEHOLDER."$PLANT_1$"=>:CUR_ROW."ZPLANT") GROUP BY "SourceSystem", "Region", "Plant", "WarehouseNumber", "StorageLocation", "SalesOrderNumber", "SalesOrderStatus" into "SCHEMA"."TAB_IVR_STG";

commit;

END FOR;

CLOSE tt_PLANTS;

END;

When i run the procedure it loops through the cursor, but insert 0 records into table "TAB_IVR_STG".

Is there any wrong in syntax or something i missed in my procedure?

Your help is really appreciated.

Thanks

Murali

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 02, 2018 at 05:16 PM

    Hi, can you try insert into and then select statement.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 05, 2018 at 05:00 AM

    Hi Murali,

    Please try the below approaches to debug the issue.

    1. Copy the select statement and pass the values manually for all input parameters used(SOURCE SYSTEM,CALMONTH,REGION etc.) and execute the query, check if the query resulting any output.

    2. Re phrase your select query i.e. select columns into temp table from DB table where condition group by columns.

    Please let me know if you still face the issue.

    Thanks,

    Jay.

    Add comment
    10|10000 characters needed characters exceeded