BFL Function #1 : working with DAYS function

[Former Member]

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