Example: SQL scalar UDFs

 

This example shows a scalar function that returns a priority based on a date.

CREATE FUNCTION PRIORITY(indate DATE) RETURNS CHAR(7)

LANGUAGE SQL BEGIN RETURN(
      CASE   WHEN indate>CURRENT DATE-3 DAYS THEN 'HIGH'
         WHEN indate>CURRENT DATE-7 DAYS THEN 'MEDIUM'
         ELSE 'LOW'
      END
      );

END

The function can then be called as:

SELECT ORDERNBR, PRIORITY(ORDERDUEDATE) FROM ORDERS

 

Parent topic:

Writing UDFs as SQL functions