The following common syntactical items are either DB2-specific and do not conform to the standard SQL syntax definitions (that is, to the Common Set of Natural SQL syntax) or impose restrictions when used with DB2 (see also SQL Statements in the Natural Statements documentation).
This section covers the following topics:
An atom can be either a parameter (that is, a Natural program variable or host variable) or a constant. When running dynamically, however, the use of host variables is restricted by DB2. For further details, refer to the relevant DB2 literature by IBM.
The comparison operators specific to DB2 belong to the Natural Extended Set. For a description, refer to Comparison Predicate in Search Conditions, Natural SQL Statements (Statements Grouped by Functions, Natural Statements documentation).
The following factors are specific to DB2 and belong to the Natural Extended Set:
|
special-register |
A scalar function is a built-in function that can be used in the construction of scalar computational expressions. Scalar functions are specific to DB2 and belong to the Natural Extended Set.
The scalar functions NDB supports are listed below in alphabetical order:
| A - H | I - R | S - Z |
|---|---|---|
|
ABS |
IDENTITY_VAL_LOCAL |
SECOND |
Each scalar function is followed by one or more scalar expressions in parentheses. The number of scalar expressions depends upon the scalar function. Multiple scalar expressions must be separated from one another by commas.
SELECT NAME
INTO NAME
FROM SQL-PERSONNEL
WHERE SUBSTR ( NAME, 1, 3 ) = 'Fri'
...
A column function returns a single-value result for the argument it receives. The argument is a set of like values, such as the values of a column. Column functions are also called aggregating functions.
The following column functions conform to standard SQL. They are not specific to DB2:
AVG
COUNT
MAX
MIN
SUM
The following column functions do not conform to standard SQL. They are specific to DB2 and belong to the Natural Extended Set.
COUNT_BIG
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR
VAR_POP
VAR_SAMP
VARIANCE
VARIANCE_SAMP
XMLAGG
The concatenation operator (CONCAT or "||") does not conform to standard SQL. It is specific to DB2 and belongs to the Natural Extended Set.
The following special registers do not conform to standard SQL. They are specific to DB2 and belong to the Natural Extended Set:
CURRENT APPLICATION ENCODING SCHEME
CURRENT CLIENT_ACCNTG
CURRENT CLIENT_APPLNAME
CURRENT CLIENT_USERID
CURRENT CLIENT_WRKSTNNAME
CURRENT DATE
CURRENT_DATE
CURRENT DEGREE
CURRENT FUNCTION PATH
CURRENT_LC_CTYPE
CURRENT LC_CTYPE
CURRENT LOCALE LC_CTYPE
CURRENT OPTIMIZATION HINT
CURRENT PACKAGESET
CURRENT_PATH
CURRENT PRECISION
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
CURRENT_MEMBER
CURRENT PACKAGE PATH
CURRENT REFRESH AGE
CURRENT SCHEMA
CURRENT RULES
CURRENT SQLID
CURRENT SERVER
CURRENT TIME
CURRENT_TIME
CURRENT TIMESTAMP
CURRENT TIMEZONE
CURRENT_TIMEZONE USER
A reference to a special register returns a scalar value.
Using the command SET CURRENT SQLID, the creator name of a table can be substituted by the current SQLID. This enables you to access identical tables with the same table name but with different creator names.
Units, also called durations, are specific to DB2 and belong to the Natural Extended Set.
The following units are supported:
DAY
DAYS
HOUR
HOURS
MICROSECOND
MICROSECONDS
MINUTE
MINUTES
MONTH
MONTHS
SECOND
SECONDS
YEAR
YEARS
CASE
|
|
searched-when-clause ... |
|
|
ELSE
|
|
|
|
|
END
|
Case-expressions do not conform to standard SQL and are therefore supported by the Natural SQL Extended Set only.
DEFINE DATA LOCAL
01 #EMP
02 #EMPNO (A10)
02 #FIRSTNME (A15)
02 #MIDINIT (A5)
02 #LASTNAME (A15)
02 #EDLEVEL (A13)
02 #INCOME (P7)
END-DEFINE
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,
(CASE WHEN EDLEVEL < 15 THEN 'SECONDARY'
WHEN EDLEVEL < 19 THEN 'COLLEGE'
ELSE 'POST GRADUATE'
END ) AS EDUCATION, SALARY + COMM AS INCOME
INTO
#EMPNO, #FIRSTNME, #MIDINIT, #LASTNAME,
#EDLEVEL, #INCOME
FROM DSN8510-EMP
WHERE (CASE WHEN SALARY = 0 THEN NULL
ELSE SALARY / COMM
END ) > 0.25
DISPLAY #EMP
END-SELECT
END