Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos
Here's a feature you might never have thought to use in your smart data streaming project: CCLScript functions. These functions are incredibly versatile, and can help you accomplish even more with your streaming project. You can use any CCLScript function, even a custom one, within a CCL query’s SELECT or WHERE clause.

There are several CCLScript use-case examples available in the SAP HANA Smart Data Streaming: Examples Guide. For this blog, I'll be using the CCL function example that is included with smart data streaming, and is covered in the CCL Function topic underneath DECLARE Block Examples.

Calculating a Weighted Average


This simple example uses a basic input window, and an output window with a custom CCLScript function.

First, you create a schema named TradeSchema for use with the input window, TradeWindow:
CREATE SCHEMA TradeSchema (
Ts BIGDATETIME,
Symbol STRING,
Price MONEY(2),
Volume INTEGER
);

Then, use a DECLARE block to create the function MyWeightedAverage, with variables of Value1 and Value2, and a local variable called Weight1:
DECLARE MONEY(2) MyWeightedAverage 
(MONEY(2) Value1, INTEGER Value2)
{
FLOAT Weight1 := 1.0;

IF (Value2 > 10000 )
{ Weight1 := 0.5; }
ELSE IF (Value2 > 4000)
{Weight1 := 0.75; }
ELSE IF (Value2 < 100)
{ Weight1 := 3.0; }
ELSE IF (Value2 < 500)
{ Weight1 := 0.25; }
RETURN to_money(Value1 * Weight1 ,2);
}
END;

Using a series of if and else if conditions, the function determines the value of Weight1 based on whether Value2 is greater or less than the specified values. Then, the resulting Weight1 value is used in the to_money() function of the RETURN statement to calculate the average.

Next, create the input window TradeWindow using TradeSchema, then create the output window OutWeightedAverage:
CREATE INPUT WINDOM TradeWindow
SCHEMA TradeSchema
PRIMARY KEY (Ts);

CREATE OUTPUT WINDOW OutWeightedAverage
SCHEMA ( Symbol STRING, avgPrice MONEY(2), wavgPrice MONEY(2))
PRIMARY KEY deduced
AS
SELECT
t.Symbol,
avg(t.Price) avgPrice,
avg(MyWeightedAverage(t.Price, t.Volume)) wavgPrice
FROM
TradeWindow t
GROUP BY t.Symbol;

The output window specifies its own inline schema, and uses MyWeightedAverage within the avg() function inside its SELECT statement.

Using the custom CCLScript function MyWeightedAverage within the output window allows the project to output more useful data by handling some of the number processing internally.

Don’t forget to use your own CCLScript functions within queries in your streaming projects to open up more possibilities for data use, and save time spent on external processing. See more examples in the SAP HANA Smart Data Streaming: Examples Guide.