Progress
Embedded SQL-92
Guide and Reference


Using Scalar Functions

Scalar functions accept as arguments a collection of values derived from one or more columns corresponding to one row, from either a database table or an intermediate result table. Scalar functions return one value.

EXAMPLES

The following examples illustrate scalar functions in SQL statements:

EXEC SQL
     SELECT order_no, product
     FROM orders
     WHERE ABS (qty - :old_qty) > 10000 ;
 
EXEC SQL
     SELECT order_no, product, qty
     FROM orders
     WHERE order_date = TO_DATE (’01/02/1993’) ;
 
EXEC SQL
     SELECT MONTHS_BETWEEN (SYSDATE, order_date)
     FROM orders
     WHERE order_no = 1005 ; 

The DECODE function accepts a column name, a column value, and its substitute value. Optionally, you can specify a default value. This example compares the deptno column to the department code, and returns the corresponding department name. If there is no match, the example returns the default string NOT ASSIGNED. The DECODE function is similar to the C Language switch statement.

EXEC SQL
     SELECT ename, DECODE
          (
          deptno,
          10, ’ACCOUNTS ’,
          20, ’RESEARCH ’,
          30, ’SALES ’,
          40, ’SUPPORT ’,
          ’NOT ASSIGNED’
          )
     FROM employee ; 

You can use the NVL function to check whether a column value is NULL. If NULL, you can specify a substitute value to return. This example returns the substitute zero value if the commission column contains a NULL value:

EXEC SQL
     SELECT salary + NVL (commission, 0)
     FROM employee ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095