The SQLScript Reference Manual for HANA contains a description for using resursive logic within procedures and functions. Recursion in procedures has a limitation that sets the maximum call depth to 32. I ran across this as I was rewriting some database-related JAVA code as an SQLScript procedure.
While this call-depth check would normally be a showstopper, it looks like with some thought and knowledge of SQLScript capabilities, the limit can be easily overcome.
I assume this limit applies to procedures and not functions because it is related to the fact that procedures can execute inserts, updates, and deletes to tables, whereas functions cannot. The origin of the limit “32” is likewise not known. It does turn out that the SAP ASE database system likewise has the same type of recursive capability and limitation even though it is not documented at all, so I would not view the HANA limitation negatively.
The code under review involves inventory cost methods for equity stock security purchases and sales. When we sell a security, FIFO, LIFO, and moving average accounting methods are applied to the 2nd part of the accounting journal entry, which is the cost part rather than the revenue part. For cost, we calculate the reduction in inventory and the cost of securities sold. The recursive goal is to figure out which securities to use for costing. Although we could use an SQL loop to select the oldest securities (in the case of FIFO), recursion is also viable. When implementing this, we have many cases where positions for more than 32 small security purchases need to be unwound, in order match one large security sale.
The solution to the recursive call-depth limit is surprising simple.
First, the design of each recursive iteration must be transactionally complete, so that the input of the next iteration does not depend on the result of the previous iteration. This disqualifies things like the factorial example in the HANA documentation. Instead, we must have a design where each iteration commits results to the database so that the next iteration can continue from a known point. If SQL functions had a call-depth limit (which they do not), they too would have been disqualified since they cannot persist results in the form of updates, inserts, and deletes to tables.
Second, we need a way to continue the recursive iteration if it stops. Fortunately, SQLScript has EXIT and CONTINUE exception handlers. With this, we can trap SQL error code 1340 and continue the recursive iteration.
Here is a sample data set, code, and report for a recursive FIFO security costing method which does not abort on a ERR_SQLSCRIPT_NESTED_CALL_TOO_DEEP error:
-- Chronological inventory of purchases (-) and sales (+)
create table inventory (
id int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) null,
position int not null);
-- Hierarchy for fifo match
create table f_fifo (
parent_id int null,
node_id int not null,
ord int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) not null);
create sequence ord start with 1 increment by 1;
insert into inventory values(1004286312,to_date('10/01/2019','MM/DD/YYYY'),-100,10.00,-100);
insert into inventory values(1004286313,to_date('10/03/2019','MM/DD/YYYY'),-100,11.00,-100);
insert into inventory values(1004286314,to_date('10/05/2019','MM/DD/YYYY'), 150,null, 150);
insert into inventory values(1004286315,to_date('10/06/2019','MM/DD/YYYY'),-100,10.80,-100);
insert into inventory values(1004286316,to_date('10/07/2019','MM/DD/YYYY'), 50,null, 50);
create or replace procedure sp_fifo (in sId int)
as
begin
declare sPosition, diff, pPosition, pId int;
declare sSettleDate date;
declare pCost decimal(10,2);
-- The oldest purchase (fifo)
select id, position, cost
into pId, pPosition, pCost default null, 0, null
from inventory where id =
(select min(id)
from inventory where position < 0);
-- The sale
select position, settleDate
into sPosition, sSettleDate default 0, null
from inventory where id = :sId;
if :pPosition < 0 and :sPositon > 0 then
diff = case
when :sPosition + :pPosition < 0 then 0
else :sPosition + :pPosition
end;
update inventory set position = position + (:sPosition - :diff) where id = :pId;
update inventory set position = :diff where id = :sId;
insert into f_fifo values (:pId,:sId,ord.nextval,:sSettleDate, :sPosition - :diff,:pCost);
call sp_fifo (:sId);
end if;
end;
create or replace procedure sp_unwind (in sId int)
as
begin
using sqlscript_print as prtlib;
using sqlscript_string as strlib;
declare msg nvarchar(5000) =
strlib:format('recursion limit {} exceeded. continuing.',32);
-- Restart fifo if recursion limit reached.
declare continue handler for sql_error_code 1340
begin
prtlib:print_line(:msg);
call sp_fifo(:sId);
end;
call sp_fifo(:sId);
end;
call sp_unwind (1004286314); -- unwind the first sale
call sp_unwind (1004286316); -- unwind the second sale
Here is the inventory input table, showing three purchases and two sales:
id settleDate ps qty cost
---------- ---------- -------- ---- -----
1004286312 2019-10-01 purchase -100 10.00
1004286313 2019-10-03 purchase -100 11.00
1004286314 2019-10-05 sale 150
1004286315 2019-10-06 purchase -100 10.80
1004286316 2019-10-07 sale 50
Here is the f_fifo table result after applying FIFO recursion. I am using a Hierarchy data structure so that I can net the notionals (qty*cost) to compute a balance. For a parent purchase node which is a (-) quantity, sales are applied to it with a (+) quantity as a child node. Child nodes are added as the sale proceeds recursively. If the purchase has been completely unwound, then the balance is 0.0. The hierarchy_descendants_aggregate() function is used to net parent purchases with matching child sales:
-- Load all purchases for matching purposes.
-- Sales were already loaded by sp_unwind().
insert into f_fifo
select null,id,ord.nextval,settleDate,qty,cost
from inventory where qty < 0
and not exists (select * from f_fifo where qty < 0);
ps parent_id node_id settleDate qty cost
-------- ---------- ---------- ---------- ---- -----
purchase null 1004286312 2019-10-01 -100 10.00
purchase null 1004286313 2019-10-03 -100 11.00
sale 1004286312 1004286314 2019-10-05 100 10.00
sale 1004286313 1004286314 2019-10-05 50 11.00
purchase null 1004286315 2019-10-06 -100 10.80
sale 1004286313 1004286316 2019-10-07 50 11.00
And here is the final report showing notionals and balances. First we create the hierarchy data structure as a view over the f_fifo table which holds the results.
-- Create hierarchy view
create view h_fifo as
select *
from hierarchy (
source f_fifo
sibling order by ord);
Then we return the reporting results:
select
case when hierarchy_aggregate_type = 0 then 'entry' else 'balance' end "type",
case when hierarchy_level = 1 then 'purchase'
when hierarchy_level != 1 and hierarchy_aggregate_type = 0 then 'sale'
else null end "ps",
node_id "id",
settleDate "settleDate",
qty "qty",
cost "cost",
qty*cost "notional",
balance "balance"
from hierarchy_descendants_aggregate (
source h_fifo
measures (
sum(qty*cost) as balance
) with total null
) order by hierarchy_aggregate_type,settleDate,ord;
type ps id settleDate qty cost notional balance
----- -------- ----------- ---------- ---- ----- -------- --------
entry purchase 1004286312 2019-10-01 -100 10.00 -1000.00 0.00
entry purchase 1004286313 2019-10-03 -100 11.00 -1100.00 0.00
entry sale 1004286314 2019-10-05 100 10.00 1000.00 1000.00
entry sale 1004286314 2019-10-05 50 11.00 550.00 550.00
entry purchase 1004286315 2019-10-06 -100 10.80 -1080.00 -1080.00
entry sale 1004286316 2019-10-07 50 11.00 550.00 550.00
balance -1080.00
With this, we show that a combination of HANA features for recursion and hierarchies can be used together in just a few lines of code to efficiently solve what used to be a considerable investment in an external JAVA application.
While this call-depth check would normally be a showstopper, it looks like with some thought and knowledge of SQLScript capabilities, the limit can be easily overcome.
I assume this limit applies to procedures and not functions because it is related to the fact that procedures can execute inserts, updates, and deletes to tables, whereas functions cannot. The origin of the limit “32” is likewise not known. It does turn out that the SAP ASE database system likewise has the same type of recursive capability and limitation even though it is not documented at all, so I would not view the HANA limitation negatively.
The Business Application
The code under review involves inventory cost methods for equity stock security purchases and sales. When we sell a security, FIFO, LIFO, and moving average accounting methods are applied to the 2nd part of the accounting journal entry, which is the cost part rather than the revenue part. For cost, we calculate the reduction in inventory and the cost of securities sold. The recursive goal is to figure out which securities to use for costing. Although we could use an SQL loop to select the oldest securities (in the case of FIFO), recursion is also viable. When implementing this, we have many cases where positions for more than 32 small security purchases need to be unwound, in order match one large security sale.
The Recursion Limit Solution
The solution to the recursive call-depth limit is surprising simple.
First, the design of each recursive iteration must be transactionally complete, so that the input of the next iteration does not depend on the result of the previous iteration. This disqualifies things like the factorial example in the HANA documentation. Instead, we must have a design where each iteration commits results to the database so that the next iteration can continue from a known point. If SQL functions had a call-depth limit (which they do not), they too would have been disqualified since they cannot persist results in the form of updates, inserts, and deletes to tables.
Second, we need a way to continue the recursive iteration if it stops. Fortunately, SQLScript has EXIT and CONTINUE exception handlers. With this, we can trap SQL error code 1340 and continue the recursive iteration.
Here is a sample data set, code, and report for a recursive FIFO security costing method which does not abort on a ERR_SQLSCRIPT_NESTED_CALL_TOO_DEEP error:
Here is the Data Set
-- Chronological inventory of purchases (-) and sales (+)
create table inventory (
id int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) null,
position int not null);
-- Hierarchy for fifo match
create table f_fifo (
parent_id int null,
node_id int not null,
ord int not null,
settleDate date not null,
qty int not null,
cost decimal(4,2) not null);
create sequence ord start with 1 increment by 1;
insert into inventory values(1004286312,to_date('10/01/2019','MM/DD/YYYY'),-100,10.00,-100);
insert into inventory values(1004286313,to_date('10/03/2019','MM/DD/YYYY'),-100,11.00,-100);
insert into inventory values(1004286314,to_date('10/05/2019','MM/DD/YYYY'), 150,null, 150);
insert into inventory values(1004286315,to_date('10/06/2019','MM/DD/YYYY'),-100,10.80,-100);
insert into inventory values(1004286316,to_date('10/07/2019','MM/DD/YYYY'), 50,null, 50);
Here is the Code
create or replace procedure sp_fifo (in sId int)
as
begin
declare sPosition, diff, pPosition, pId int;
declare sSettleDate date;
declare pCost decimal(10,2);
-- The oldest purchase (fifo)
select id, position, cost
into pId, pPosition, pCost default null, 0, null
from inventory where id =
(select min(id)
from inventory where position < 0);
-- The sale
select position, settleDate
into sPosition, sSettleDate default 0, null
from inventory where id = :sId;
if :pPosition < 0 and :sPositon > 0 then
diff = case
when :sPosition + :pPosition < 0 then 0
else :sPosition + :pPosition
end;
update inventory set position = position + (:sPosition - :diff) where id = :pId;
update inventory set position = :diff where id = :sId;
insert into f_fifo values (:pId,:sId,ord.nextval,:sSettleDate, :sPosition - :diff,:pCost);
call sp_fifo (:sId);
end if;
end;
create or replace procedure sp_unwind (in sId int)
as
begin
using sqlscript_print as prtlib;
using sqlscript_string as strlib;
declare msg nvarchar(5000) =
strlib:format('recursion limit {} exceeded. continuing.',32);
-- Restart fifo if recursion limit reached.
declare continue handler for sql_error_code 1340
begin
prtlib:print_line(:msg);
call sp_fifo(:sId);
end;
call sp_fifo(:sId);
end;
Here is how to Run it
call sp_unwind (1004286314); -- unwind the first sale
call sp_unwind (1004286316); -- unwind the second sale
And here is the Report
Here is the inventory input table, showing three purchases and two sales:
id settleDate ps qty cost
---------- ---------- -------- ---- -----
1004286312 2019-10-01 purchase -100 10.00
1004286313 2019-10-03 purchase -100 11.00
1004286314 2019-10-05 sale 150
1004286315 2019-10-06 purchase -100 10.80
1004286316 2019-10-07 sale 50
Here is the f_fifo table result after applying FIFO recursion. I am using a Hierarchy data structure so that I can net the notionals (qty*cost) to compute a balance. For a parent purchase node which is a (-) quantity, sales are applied to it with a (+) quantity as a child node. Child nodes are added as the sale proceeds recursively. If the purchase has been completely unwound, then the balance is 0.0. The hierarchy_descendants_aggregate() function is used to net parent purchases with matching child sales:
-- Load all purchases for matching purposes.
-- Sales were already loaded by sp_unwind().
insert into f_fifo
select null,id,ord.nextval,settleDate,qty,cost
from inventory where qty < 0
and not exists (select * from f_fifo where qty < 0);
ps parent_id node_id settleDate qty cost
-------- ---------- ---------- ---------- ---- -----
purchase null 1004286312 2019-10-01 -100 10.00
purchase null 1004286313 2019-10-03 -100 11.00
sale 1004286312 1004286314 2019-10-05 100 10.00
sale 1004286313 1004286314 2019-10-05 50 11.00
purchase null 1004286315 2019-10-06 -100 10.80
sale 1004286313 1004286316 2019-10-07 50 11.00
And here is the final report showing notionals and balances. First we create the hierarchy data structure as a view over the f_fifo table which holds the results.
-- Create hierarchy view
create view h_fifo as
select *
from hierarchy (
source f_fifo
sibling order by ord);
Then we return the reporting results:
select
case when hierarchy_aggregate_type = 0 then 'entry' else 'balance' end "type",
case when hierarchy_level = 1 then 'purchase'
when hierarchy_level != 1 and hierarchy_aggregate_type = 0 then 'sale'
else null end "ps",
node_id "id",
settleDate "settleDate",
qty "qty",
cost "cost",
qty*cost "notional",
balance "balance"
from hierarchy_descendants_aggregate (
source h_fifo
measures (
sum(qty*cost) as balance
) with total null
) order by hierarchy_aggregate_type,settleDate,ord;
type ps id settleDate qty cost notional balance
----- -------- ----------- ---------- ---- ----- -------- --------
entry purchase 1004286312 2019-10-01 -100 10.00 -1000.00 0.00
entry purchase 1004286313 2019-10-03 -100 11.00 -1100.00 0.00
entry sale 1004286314 2019-10-05 100 10.00 1000.00 1000.00
entry sale 1004286314 2019-10-05 50 11.00 550.00 550.00
entry purchase 1004286315 2019-10-06 -100 10.80 -1080.00 -1080.00
entry sale 1004286316 2019-10-07 50 11.00 550.00 550.00
balance -1080.00
With this, we show that a combination of HANA features for recursion and hierarchies can be used together in just a few lines of code to efficiently solve what used to be a considerable investment in an external JAVA application.
No comments:
Post a Comment