Skip to Content

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

T1.JPG

T2.JPG

–Regular SQL logic

SELECT “ORDERDATEKEY” AS “FROMDATE”,

       CURRENT_DATE   AS “TODATE”,

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

      FROM “HANAGA”.“INTERNETSALES”;

T3.JPG

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”;   

T4.JPG

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply