BFL Function #1 : working with DAYS function

Hello Everyone,

                here I am demonstrating the BFL function “DAYS”

to Calculate number of days between every Sales Order date and Current System date

My Database table “INTERNETSALES” contains approximately 60,000 records

I have a Date Column “ORDERDATEKEY”

–Regular SQL logic

SELECT “ORDERDATEKEY” AS “FROMDATE”,

       CURRENT_DATE   AS “TODATE”,

       DAYS_BETWEEN( “ORDERDATEKEY”, CURRENT_DATE ) AS “TOTALDAYS”

      FROM “HANAGA”.“INTERNETSALES”;

With regular SQL I got the result in 15ms

Now, let us see how much time it takes if it is implemented with BFL functions “DAYS”

Here I created a Custom Procedure that executes Procedure related to BFL function “DAYS”

–BFL Function “DAYS”

–to find number of days between every Sales date of table INTERNETSALES

–and Current date of the System and

–to combine the result of DAYS functions with Sales date column

DROP PROCEDURE   “HANAGA”.“BFL_DAYS_EXAMPLE”;

CREATE PROCEDURE “HANAGA”.“BFL_DAYS_EXAMPLE”

LANGUAGE SQLSCRIPT

READS SQL DATA

AS

BEGIN

tab1 = CE_COLUMN_TABLE( “HANAGA”.“INTERNETSALES” );

tab2 = CE_PROJECTION(

             :tab1,

             [“ORDERDATEKEY”,

              CE_CALC( ‘ leftstr(string(“ORDERDATEKEY”),4) + midstr(string(“ORDERDATEKEY”),6,2) + midstr(string(“ORDERDATEKEY”),9,2) ‘, VARCHAR(8) ) AS “FROMDATE”]   

       );

tab3 = CE_PROJECTION(

             :tab1,

             [ “ORDERDATEKEY”,

               CE_CALC( ‘ leftstr(string(now()),4) + midstr(string(now()),6,2) + midstr(string(now()),9,2) ‘, varchar(8) ) AS “TODATE”]   

       );

tab4 = CE_PROJECTION(

             :tab1,

             [ “ORDERDATEKEY”,

               CE_CALC( ‘ 0 ‘, DOUBLE ) AS “CONFIG”

             ]   

       );

fromdate_tab =  CE_PROJECTION(

                        :tab2,

                        [ “FROMDATE” ]

                        );  

todate_tab =  SELECT “TODATE” FROM :tab3;

config_tab =  SELECT “CONFIG” FROM :tab4;

CALL “_SYS_AFL”.“AFLBFL_DAYS_PROC”(

:fromdate_tab,

:todate_tab,

                                    :config_tab,

                                    :out_tab

                                  );

o_tab = CE_VERTICAL_UNION(  :fromdate_tab, [“FROMDATE”],

                            :todate_tab, [“TODATE”],

                            :out_tab, [“TOTALDAYS”]

                         );

SELECT * FROM :o_tab;

END;

–Executing above procedure

CALL “HANAGA”.“BFL_DAYS_EXAMPLE”;   

With BFL function and pure SQLScript I expected quick result. But it has taken more time.

But, this observation has given me good knowledge on CE_CALC and CE_PROJECTION functions

Enjoy HANA

Regards

Nagababu Tubati