A scalar function receives one value and produces one value.
It works row by row, unlike an aggregate function which works on a group of rows.
Scalar functions can be used in the SELECT list, WHERE clause, ORDER BY, GROUP BY and HAVING clause.
They are used to convert, format or extract parts of column values.
Commonly used scalar functions
CHAR - string representation of its argument, for example CHAR(HIREDATE).
DATE, TIME, TIMESTAMP - derive a date, time or timestamp from the argument, for example DATE('1977-12-16').
DAY, MONTH, YEAR - extract parts of a date, for example YEAR(BIRTHDATE).
DAYS - integer representation of a date, useful for date arithmetic like DAYS('1979-11-02') - DAYS('1977-12-16').
DECIMAL, FLOAT, INTEGER - numeric conversion, for example DECIMAL(AVG(SALARY), 8, 2).
LENGTH - length of its argument, for example LENGTH(ADDRESS).
SUBSTR - sub-string of a string, for example SUBSTR(FSTNAME, 1, 3).
VALUE - returns the first argument that is not null, for example VALUE(COMM, 0).
HEX - hexadecimal representation of its argument.
Example
Scalar functions used in a SELECT statement:-
-- String functions: first 3 letters of name, length of address
SELECT SUBSTR(FNAME, 1, 3), LENGTH(ADDR)
FROM MM01.CUSTOMER;
-- Date part functions: year, month and day of hire date
SELECT YEAR(HIREDATE), MONTH(HIREDATE), DAY(HIREDATE)
FROM EMPLOYEE;
-- Numeric conversion and null handling
SELECT DECIMAL(AVG(SALARY), 9, 2), VALUE(COMM, 0)
FROM EMPLOYEE;
Mainframe Tutorial
JCL Tutorial
Introduction
Mainframe Interview Q&A
Mainframe Quiz
Mainframe Memes
Contact us
About us
Backward
