Binding table var to unnest array in calc view

[patrickbachmann]

Hi folks,

I have a calculation view where I'm building an array and at the end I unnest and bind to my var_out.

Something like this;

VAR_OUT = UNNEST(:SCARR_COMPCODE,:SCARR_DOCNUM,:SCARR_CUSTOMER, :SCARR_PROFIT_CTR, :SCARR_AMT) AS ("COMPCODE","DOCNUM","CUSTOMER", "PROFIT_CTR", "AMOUNT");

Now what I'd like to do is I would like to join some other tables to this unnested array so I figured I would do something like;

tempVar = UNNEST(:SCARR_COMPCODE,:SCARR_DOCNUM,:SCARR_CUSTOMER, :SCARR_PROFIT_CTR, :SCARR_AMT) AS ("COMPCODE","DOCNUM","CUSTOMER", "PROFIT_CTR", "AMOUNT");

VAR_OUT = select * from tempVar inner join table abc on condition...

But the binding of tempVar is failing and I'm getting error 'could not find table tempVar in schema SYSTEM.'  Normally i'm binding table variables like this all the time like this;

tempVar = select * from TABLE1

In this case HANA automatically knows it's a table variable.  Yet for some reason the UNNEST is not automatically considered a tableVar yet looking at the SQL guide it says the output of UNNEST is a table specification (<return_table_specification>).

Is this not possible to do what I'm attempting or do I somehow have to manually declare tempVar as a table variable when using unnest?

Thanks,

-Patrick

[pfefferf · Accepted solution]

Hi Patrick,

you have to address the internal table with a ":".

var_out = select * from :tempVar as t1 inner join "<schema>".abc as t2 on t1.x = t2.x;

Regards,

Florian

[patrickbachmann · Accepted solution]

Hi Florian,

I'm sorry, just to clarify my VAR_OUT is just sort of meaningless pseudocode for you to see the order of events i'm doing but it's the setting of tempVar = the unnest that is the issue that's getting the error. 

Thanks,

-Patrick

[pfefferf · Accepted solution]

Hi Patrick,

thx for clarification.

I tested your scenario with some dummy data in a scripted calculation view. The result is, that the UNNEST works as expected. Also the following join between the internal table and a catalog table works.

The only situation in which I could reproduce the exact same error than you mentioned in your original post is, when I remove the ":" from the ":tempVar" in the join. Because then the with the name of the "internal" table is searched in the schema and not found.

Regards,

Florian

[patrickbachmann · Accepted solution]

Florian, can you post your code or at least just the table variable and var_out part?

Thanks!

[patrickbachmann · Accepted solution]

Oh also what rev are you on?  I'm on SPS8 rev 82 on this particular system at least.

Thanks again.

[pfefferf · Accepted solution]

Here is my misc coding:

/********* Begin Procedure Script ************/
BEGIN
  declare aC1 nvarchar(256) array;
  declare aC2 integer array;

  aC1[1] = 'Test 1';
  aC1[2] = 'Test 2';

  aC2[1] = 1;
  aC2[2] = 2;

  lt_unnested = unnest(:aC1, :aC2) as ("C1", "C2");

  lt_test1 = select * from :lt_unnested as t1
            inner join "MISC"."MISC_NVARCHAR" as t2
            on t1."C2" = t2."ID";

  lt_test2 = select * from "MISC"."MISC_NVARCHAR";



  var_out = select * from :lt_unnested;

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

I have tested it on rev 102. But the unnest also works on 82. We have used it already on that rev level within customer projects.

[patrickbachmann · Accepted solution]

Fabian my apologies but I think I misinterpreted your first response.  I just created a new calc view using your code and it worked.  So then I scratched my head and looked at my original view and sure enough I didn't have the colon in front of my tempVar;

VAR_OUT = select * from :tempVar inner join table abc on condition...

My brain must have short-wired that day when I read your first reply. 

Thanks!!

-Patrick