cancel
Showing results for 
Search instead for 
Did you mean: 

Table function returning different result when called from calculation view

upamanyu_mukherjee
Participant
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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;
upamanyu_mukherjee
Participant
0 Kudos

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

Answers (0)