Using Scalar Functions

Scalar functions take 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 and returns one value.

Salar Functions used in c-treeSQL 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 the column name, column value and its substitute value. Optionally, a default value can be specified.

DECODE Example

SELECT ename, DECODE (deptno,

                    10, 'ACCOUNTS    ',

                    20, 'RESEARCH    ',

                    30, 'SALES       ',

                    40, 'SUPPORT     ',

                    'NOT ASSIGNED'

               )

FROM employee ;

In the above example, the column deptno is compared with the department code and the corresponding department name is returned. If no match is found, the default value, NOT ASSIGNED, is returned. The DECODE function is similar to the switch statement in C.

The NVL function can be used to check whether a column value is NULL. If NULL, a substitute value can be returned. Consider the following example:

EXEC SQL

     SELECT salary + NVL (commission, 0)     

     FROM employee ;

In the above example, a zero value is substituted if the column commission contains NULL value.