Multiplying two columns by CE functions

[former_member185511]

Hi all,

I want to compare performance results between SQL script and CE functions.

I have 2 tables joined and i am multiplying 2 columns by using subquery. Tables are like that;

TABLE: SALES

MATERIALID -------- TOTAL

TABLE: PRICES

MATERIALID -------- UNITP

query is similar like

Select PRICES.MATERIALID, Prices.UNITP * (select sum(TOTAL) from SALES
where PRICES.MATERIALID = SALES.MATERIALID ) as TOTAL_AMOUNT
from PRICES.

is there a way to do it via CE functions ?

Thank you.

[former_member185511]

Hi all,

I want to compare performance results between SQL script and CE functions.

I have 2 tables joined and i am multiplying 2 columns by using subquery. Tables are like that;

TABLE: SALES

MATERIALID -------- TOTAL

TABLE: PRICES

MATERIALID -------- UNITP

query is similar like

Select PRICES.MATERIALID, Prices.UNITP * (select sum(TOTAL) from SALES
where PRICES.MATERIALID = SALES.MATERIALID ) as TOTAL_AMOUNT
from PRICES.

is there a way to do it via CE functions ?

Thank you.

[jasmin_gruschke]

Hi Bilen,
we're more focussed on the ABAP on HANA topics here. You might get a larger audience of SQLScript experts in .

Cheers,

  Jasmin

[former_member185511]

actually i couldn't get reply for 2 days there so i moved topic here(here looks more dynamic) eheheh. May be not possible i am also checking.

Thank you.

[Former Member]

Hi Bilen, you could try something like the below:

t_sales = CE_COLUMN_TABLE("SALES", ["MATERIALID","TOTAL"]);

t_sales_agg = CE_AGGREGATION(:t_sales, [SUM("TOTAL") AS "TOT"], ["MATERIALID"]);

t_prices = CE_COLUMN_TABLE("PRICES", ["MATERIALID","UNITP"]);

t_sales_prices_join = CE_JOIN(:t_sales_agg,:t_prices,["MATERIALID"],["MATERIALID", "UNITP", "TOT"]);

t_sales_total = CE_PROJECTION(:t_sales_prices_join, ["MATERIALID", CE_CALC('"UNITP" * "TOT"', decimal(10,2)) AS "TOTAL_AMOUNT"]);

I haven't tested it but if you play around with it you should get it to work.

Peter

[former_member185511]

thanks Peter i will try and let you know, btw can we use temp tables in thise CE functions instead of :table  ?

[Former Member]

Hi Bilen, no not that I am aware. Please see the SQL Script reference for usage - https://help.sap.com/hana_appliance#section7

If using temp tables then maybe pass in the temp table as a table parameter to the procedure and use the table variable in the CE functions. Remember not to mix SQL and CE functions in the same procedure.

Peter