Version 4.2.6 for Mainframes
 —  Database Management System Interfaces  —

Natural SQL Statements - Syntactical Items

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:


atom

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.

Top of page

comparison

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).

Top of page

factor

The following factors are specific to DB2 and belong to the Natural Extended Set:

special-register
scalar-function (scalar-expression, ...)
scalar-expression unit case-expression

Top of page

scalar-function

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
ABSVAL
ACOS
ADD_MONTHS
ASIN
ATAN
ATAN2
ATANH
BLOB
CCSID_ENCODING
CEIL
CEILING
CHAR
CHARACTER_LENGTH
CLOB
COALESCE
CONCAT
COS
COSH
DATE
DAY
DAYOFMONTH
DAYOFWEEK
DAYOFWEEK_ISO
DAYOFYEAR
DAYS
DBCLOB
DEC
DECIMAL
DECRYPT_BIT
DECRYPT_CHAR
DECRYPT_DB
DEGREES
DIGITS
DOUBLE
DOUBLE_PRECISION
ENCRYPT_TDES
ENCRYPT
EXP
FLOAT
FLOOR
GRAPHIC
GENERATE_UNIQUE
GETHINT
GETVARIABLE
HEX
HOUR

IDENTITY_VAL_LOCAL
IFNULL
INSERT
INTEGER
JULIAN_DAY
LAST_DAY
LCASE
LEFT
LENGTH
LN
LOCATE
LOG
LOG10
LOWER
LTRIM
MAX
MICROSECOND
MIDNIGHT_SECONDS
MIN
MINUTE
MOD
MONTH
MQPUBLISH
MQPUBLISHXML
MQREAD
MQREADCLOB
MQREADXML
MQRECEIVE
MQRECEIVECLOB
MQRECEIVEXML
MQSEND
MQSENDXML
MQSENDXMLFILE
MQSENDXMLFILECLOB
MQSUBSCRIBE
MQUNSUBSCRIBE
MULTIPLY_ALT
NEXT_DAY
NULLIF
POSSTR
POWER
QUARTER
RADIANS
RAISE_ERROR
RAND
REAL
REPEAT
REPLACE
RIGHT
ROUND
ROUND_TIMESTAMP
ROWID
RTRIM

SECOND
SIGN
SIN
SINH
SMALLINT
SPACE
SQRT
STRIP
SUBSTR
SUBSTRING
TAN
TANH
TIME
TIMESTAMP
TIMESTAMP_FORMAT
TO_CHAR
TO_DATE
TRANSLATE
TRUNC
TRUNC_TIMESTAMP
TRUNCATE
UCASE
UPPER
VALUE
VARCHAR
VARCHAR_FORMAT
VARGRAPHIC
WEEK
WEEK_ISO
XMLATTRIBUTES
XMLCONCAT
XMLELEMENT
XMLFOREST
XMLNAMESPACES
XML2CLOB
YEAR

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.

Example:

SELECT NAME 
  INTO NAME
  FROM SQL-PERSONNEL
  WHERE SUBSTR ( NAME, 1, 3 ) = 'Fri'
       ...

Top of page

column-function

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

Top of page

scalar-operator

The concatenation operator (CONCAT or "||") does not conform to standard SQL. It is specific to DB2 and belongs to the Natural Extended Set.

Top of page

special-register

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.

Top of page

units

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

Top of page

case-expression

CASE

searched-when-clause ...
simple-when-clause

ELSE

NULL
scalar expression

END

Case-expressions do not conform to standard SQL and are therefore supported by the Natural SQL Extended Set only.

Example:

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

Top of page