02 - DB2 for i (EN)02a - SQL (EN)02c - Miscellanea DB2 for i (EN)

Smarter SQL functions with control statements

Often we write SQL functions with the purpose of getting the result of a simple query, or to interface procedures of RPG service programs containing more complex business logic.
But SQL can do much more than simple SELECT or UPDATE… thanks to some particular statements we can implement procedural logic even within SQL functions. These instructions are called “control statements” and are not so different from the common control instructions we find in RPG. With these instructions we can add “intelligence” and flexibility to our SQL functions, which can then carry out more complex processing even without using an RPG program or service program.
SQL control instructions are documented in the SQL Reference manual at this link.
Among these statements, many of them look, so to speak, “familiar”… IF, FOR, LEAVE and others are identical (or very similar) to the corresponding RPG or CL instructions. This list can roughly be divided into three categories:
Flow control instructions: CASE, FOR, GOTO, IF, ITERATE, LEAVE, LOOP, REPEAT, RETURN, WHILE
Information and error reporting instructions: GET DIAGNOSTICS, SIGNAL, RESIGNAL
Other instructions: CALL, INCLUDE, PIPE

Control instructions can be used within SQL functions, but also in other typical SQL objects such as procedures and triggers.
After the theory, let’s move on to the practice … here we have an SQL function that returns the stock balance at a given date for an item. The function reads the current balance from a file, and from a stock transactions file it reads backwards up to the desired date, calculating the balance.
Here is the source:

--
-- function getSaldo
--
--  Returns the stock balance at a given date for an item
--
--  Parameters:
--  IN   item code      CHAR(15)
--  IN   warehouse code CHAR(2)
--  IN   date           DATE
--
--  OUT  stock balance  NUMERIC(10, 2)
--      
--
CREATE OR REPLACE FUNCTION getSaldo (
pCodItem    CHAR(40),
pCodWhs     CHAR(02),
pDateBal    DATE
)
RETURNS   NUMERIC(10, 2)

LANGUAGE SQL
READS SQL DATA
BEGIN

DECLARE   balance DECIMAL(10, 2) ;
SET       balance = 0 ;
-- Gets current stock balance
SET  balance = (
 SELECT SUM(giacenza)
FROM    SalMag00f
WHERE   articolo = pCodItem
  AND   (codMag  = pCodWhs OR pCodWhs = '  ')
)
;

--- If the input date is equal or higher than current date,
--- there is no need to process transactions
IF pDateBal < CURRENT_DATE THEN
--- Reads stock transactions back to input date
FOR c1 CURSOR FOR
SELECT
 CASE WHEN segno  = '+' THEN quantita ELSE -1 * quantita END quantita
FROM    MovMag00f
WHERE   articolo = pCodItem
  AND   dataMovimento >= pDateBal
  AND   (codMag = pCodWhs OR pCodWhs = '  ')
ORDER BY articolo, dataMovimento DESC

  DO
    SET  balance = balance - quantita ;

END FOR ;

END IF;

IF balance < 0 THEN
   SET balance = 0;
END IF ;

RETURN balance;

END;

After declaring the variables, the function retrieves the current balance from file SALMAG00F. If a blank warehouse code is passed, all warehouses are processed.
Right after, the first control statement comes into action… with an IF we check the value of the date parameter and we move on only if that is before the current date (otherwise, the stock balance is already available).
The warehouse transactions are read with a cursor embedded into a FOR cycle, which has the interesting capability of working directly as a “driver” of a fetch loop with cursor. The DO statement encloses all the statements to be executed for each single row returned by the cursor. In our case, we subtract from the balance the transaction quantity, whose sign has already been properly set in the fetch statement for file MOVMAG00F.
Finally, as a further check, in case the balance is negative, it is set to zero (negative stock balance should never happen, but you never know…)

We have seen how to create a function to return a single value. And what if we wanted to return a table? Nothing easier… if the business logic is not so complex to require building “physical” work tables (even temporary), SQL provides the handy PIPE control instruction, which allows you to populate a “virtual” table, which will then be returned as output.
Based on the function just seen before, we can create another one that returns a table row with the stock balance along with purchases and sales balance for an item at a given date.
We just show the differences with the UDF seen before.
First of all the interface declaration changes: it receives the same parameters, but since this is a UDTF it will return a table, whose structure is specified in the return parameter:

CREATE OR REPLACE FUNCTION getSaldoT (      
pCodItem    CHAR(40),                       
pCodWhs     CHAR(02),                       
pDateBal    DATE                            
)                                           
RETURNS TABLE (                             
balanceWhs      DECIMAL(10, 2),             
balanceBuy      DECIMAL(10, 2),             
balanceSell     DECIMAL(10, 2)              
)  

After declaring the variables that will hold the three balances (let’s call them balanceW, balanceP, balanceS), and after executing the same instructions as in the previous function, with two further fetch loops over the purchase and sales order detail files we will also get the other two balances.
Finally, we write a row in our “virtual” table with PIPE:

-- Append results to virtual table                   
PIPE   (balanceW, balanceP, balanceS ) ; 
 
RETURN ;                                           
 
END;  

In this case, you use RETURN without any parameters. With this example we have returned a single table row, but nothing prevents you from using PIPE for appending more rows and output a multi-row table.
For convenience we did not list here the whole source, you can download it from GitHub, along with the other UDF (see link below).

The control statements greatly expand the scope of SQL functions; thanks to them in many cases you can perform your business logic inside a function without necessarily having to write a program or an RPG service program procedure.

Documentation:
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzsqlcontstmts.htm
GitHub FAQ400 repository:
https://github.com/MD2706GIT/FAQ400

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

Leave a Reply

%d bloggers like this: