Skip to Content

Table function returning different result when called from calculation view

Hi Gurus,

I am facing an issue while using a table function that I have created in a calculation view.

The result set that I get on executing the table function using select statement is different from what I get when I use the same table function inside a calculation view .

1.Executing table function using SQL statement -

SELECT "MANDT", "PARENT", "CHILD", "LEVEL", "SETCLASS", "SUBCLASS", "VALFROM", "VALTO" FROM "schema"."pkg:VTF_HIERARCHY"() --- as per expected result

2.Executing table function from a calculation view-

Selected the same table function in a projection node in a calculation view and did data preview , the result is different and not as per expectation.The same issue is occurring while consuming the function in a scripted calculation view as well!

Seems that the execution of the table function is getting impacted!

Why is the table function returning different result when called from a calculation view?

I am currently on HANA 2.0 sp1 and my studio version is 2.3.23.

Attaching the code for reference.The code uses standard SAP tables , so should be fairly use to replicate.

Will be really grateful if someone can share inputs!

tablefunction.txt

Thanks!

Upamanyu

Add comment
10|10000 characters needed characters exceeded

  • What's the expected result? What do you get when running data preview in the modeler? What's the main difference?

    Also: why is someone still enumerating variable names? That's a recipe for making code hard to understand correctly.

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jul 12, 2017 at 09:04 PM

    My wild guess is querry optimization is happening differently. Try by using Sequential Execution option

    FUNCTION "Schema"."pkg::ZTF_HIERARCHY" () 
    	RETURNS TABLE(MANDT NVARCHAR(3),
    	PARENT NVARCHAR(100),
    	CHILD NVARCHAR(100),
    	LEVEL INTEGER,
    	SETCLASS NVARCHAR(10),
    	SUBCLASS NVARCHAR(100),
    	VALFROM NVARCHAR(20),
    	VALTO NVARCHAR(20)
    	)
    	LANGUAGE SQLSCRIPT
    	SQL SECURITY definer AS
    BEGIN SEQUENTIAL EXECUTION -- Try this
    declare v_cnt integer;
    DECLARE V_LVL INTEGER := 1;
    
    Add comment
    10|10000 characters needed characters exceeded

    • Will try this and let you know!

      What I found out while debugging was that the variable var1_1(select * from :var1;) which I am reusing at the end in the union was picking up the latest value of var1(value of var1 at the end of the loop) instead of storing the initial value(before the loop started).So instead of initializing the variable var1_1 as select * from var1, I just assigned the underlying query for var1 directly to var1_1 as well and that seemed to work!

      Earlier -

      var1_1=select * from :var1;

      Now-

      var1_1 = select mandt,null as parent,setname,:V_LVL as lvl, setname as subsetname,subclass,setclass from schema.SETHEADER a Where a.SETNAME = 'BE01' and a.SUBCLASS = 'BE01' AND a.SETCLASS = '0101';

      Is this normal behaviour?

      Thanks!!

      Upamanyu