Last Updated on 1 December 2019 by Roberto De Pedrini
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
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
