Statistics calculations in SQLScript

[Former Member]

Hi,

I have been working on SQLScript for a month or so and have been able to create reasonably complex queries. Now I have come across a scenario where I need to build a nested query that uses one of the aggregate fields calculated by the initial / outer query. So far HANA is refusing to activate the view, saying that MeanX is not a valid column name in the sub query. Please refer to the code attached. Any help would be very appreciated.

Side question: How do you declare a variable of type table in a procedure? It doesn't work like for non table types where we can just write ' temp integer' to declare an integer variable.

var_out = select kunnr "KUNNR",

AVG(cons) as meanx ,

AVG(events) as meany ,

( Select  AVG(cons - meanx)*(events - meany) as cov 

from "DATA_SLT_IDD800"."ZDATA" as B

where b.kunnr = a.kunnr

)

from "DATA_SLT_IDD800"."ZDATA" as A

group by kunnr   

;

[rama_shankar3 · Accepted solution]

Adheel:

The answer to your side question: Table types are variable structures by themselves that have a group of fields with respective data types. You can not define variables for table types. You define table types outside the procedure using SQL and then use the table types in the procedure as in or out parameter.

Hope this helps.

Regards,

Rama

[Former Member]

Hi Adeel,

I tried your issue and successfully get the result with some changes in code.

Hopefully it will not disturb your logic.

create type "schema"."TEST_VIEW" like table (

      "KUNNR" VARCHAR (2) null,

      "MEANX" DECIMAL (13,2) null,

      "MEANY" DECIMAL (13,2) null,

      "COV" DECIMAL (13,2) null);

CREATE PROCEDURE TEST(OUT var_out test_view)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW schema.ProcView AS

BEGIN

var_out = select KUNNR, AVG(cons) as Meanx , AVG(events) as Meany ,

( Select  avg((cons - meanx)*(events - meany) )

  from " schema ".testTable as B

  where b.kunnr = a.kunnr

) as cov 

from " schema ".testTable as A

group by Kunnr

;

END;

[Former Member]

Hi Ruchi,

I have created the table type like you have specified and tried to make the procedure. HANA returns the same old error message..

SAP DBTech JDBC: [260] (at 277): invalid column name: MEANX: line 11 col 30 (at pos 277)

Basically it still does not recognise the alias meanx. Another way of putting the above query would be as below. But the code below takes ages to run.

select kunnr "KUNNR",

(Select

AVG(

    (cons -

    (Select AVG(cons) from "schema"."DATA" as C where c.kunnr=b.kunnr))*

    (events -

    (Select AVG(events) from "schema"."DATA" as D where d.kunnr=b.kunnr))

)

from "schema"."DATA" as B

where b.kunnr = a.kunnr

) / STDDEV(cons) * STDDEV(events) "CORRELATION"

from "schema"."DATA" as A

group by kunnr, meterid     

;

[Former Member]

Hi Adeel

can you make the below modification and try whether it works or not.

var_out = select kunnr "KUNNR",

AVG(cons) as meanx ,

AVG(events) as meany ,

( Select  AVG(cons - a.meanx)*(events - a.meany) as cov 

from "DATA_SLT_IDD800"."ZDATA" as B

where b.kunnr = a.kunnr

)

from "DATA_SLT_IDD800"."ZDATA" as A

group by kunnr   

Awaiting your feedback.

Thanks

Santosh

[Former Member]

Hi Adeel,

Same code is working in my system. Can you check have you put one extra small braces on inner most AVG function and your MEANX column name in your table.

Also check on which meanx it throwing an error on alias one which will replicate to table type we have created or on inner meanx which come from you table.

I have also seen your code what you have pasted its working slow because you use number of subqueries and subqueries degrade the performance if you are using aggregation function inside it because inner most statement execute number of times (=number of rows the table contain) and then the outer query also using aggregation function so it will so take time to process the query.

-Ruchi

Message was edited by: Ruchi Jain

[former_member93896]

Hello Adeel,

I think the issus is that you use an expression inside the AVG function. Try to use a generated column for the expression (add it using ALTER TABLE).

Table types need to be created separately in the schema or catalog. Then you can reference the type in your procedures.

Regards,
Marc
SAP Customer Solution Adoption (CSA)

[Former Member]

Hi Marc,

The issue here is that the calculations performed on meanx are being used in a sub query. So I basically dont want the compiler to run through the same calculation unnecessarily. This is just an attempt to optimize the query for better performance.

The Alter table or schema update cant be used because my table here is being replicated via SLT from an ECC box.

Thanks for helping.