topic Re: Multiplying two columns by CE functions in Application Development and Automation Discussions

Multiplying two columns by CE functions

former_member185511 — Wed, 09 Apr 2014 05:58:13 GMT

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.

Re: Multiplying two columns by CE functions

jasmin_gruschke — Thu, 10 Apr 2014 16:08:06 GMT

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

Re: Multiplying two columns by CE functions

former_member185511 — Thu, 10 Apr 2014 16:09:44 GMT

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.

Re: Multiplying two columns by CE functions

Former Member — Thu, 24 Apr 2014 09:11:11 GMT

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

Re: Multiplying two columns by CE functions

former_member185511 — Thu, 24 Apr 2014 14:33:57 GMT

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

Re: Multiplying two columns by CE functions

Former Member — Thu, 24 Apr 2014 14:47:00 GMT

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