The so-called "Flexible SQL", which is a further possibility of issuing SQL statements, enables you to use arbitrary SQL syntax.
This document covers the following topics:
In addition to the SQL syntax described in the previous sections, flexible SQL enables you to use arbitrary SQL syntax.
<< and >>
Flexible SQL is enclosed in << and >> characters.
It can include arbitrary SQL text and host variables. Within flexible SQL, host variables
must be prefixed by a colon (:).
The flexible SQL string can cover several statement lines. Comments are possible, too
(see also the statement PROCESS
SQL).
Flexible SQL can be used as a replacement for any of the following syntactical SQL items:
Flexible SQL can also be used between the clauses of a select expression:
SELECT selection << ... >> INTO ... FROM ... << ... >> WHERE ... << ... >> GROUP BY ... << ... >> HAVING ... << ... >> ORDER BY ... << ... >>
Note:
The SQL text used in flexible SQL is not recognized by the Natural compiler. The SQL
text (with replaced host variables) is simply copied into the SQL string passed to the
database system. Syntax errors in flexible SQL are detected at runtime when the database
executes the corresponding statement.
SELECT NAME FROM SQL-EMPLOYEES WHERE << MONTH (BIRTH) >> = << MONTH (CURRENT_DATE) >>
SELECT NAME FROM SQL-EMPLOYEES WHERE << MONTH (BIRTH) = MONTH (CURRENT_DATE) >>
SELECT NAME FROM SQL-EMPLOYEES WHERE SALARY > 50000 << INTERSECT SELECT NAME FROM SQL-EMPLOYEES WHERE DEPT = 'DEPT10' >>
Within flexible SQL, you can also specify so-called "text variables".
<<:T:host-variable [LINDICATOR:host-variable]>>
|
The syntax items are described below:
:T:
|
A text variable is a At runtime, a text variable within an SQL statement will be replaced by its contents that is, the text string contained in the text variable will be inserted into the SQL string. After the replacement, trailing blanks will be removed from the inserted text string. You have to make sure yourself that the content of a text variable results in a
syntactically correct SQL string. In particular, the content of a text variable
must not contain A statement containing a text variable will always be executed in dynamic SQL mode. |
LINDICATOR
|
LINDICATOR Option:
The text variable can be followed by the keyword The length indicator variable has to be of format/length I2. If no If you specify a See general information on |
DEFINE DATA LOCAL
01 TEXTVAR (A200)
01 TABLES VIEW OF SYSIBM-SYSTABLES
02 NAME
02 CREATOR
END-DEFINE
*
MOVE 'WHERE NAME > ''SYS'' AND CREATOR = ''SYSIBM''' TO TEXTVAR
*
SELECT * INTO VIEW TABLES
FROM SYSIBM-SYSTABLES
<< :T:TEXTVAR >>
DISPLAY TABLES
END-SELECT
*
END
The generated SQL statement will look as follows:
SELECT NAME, CREATOR FROM SYSIBM.SYSTABLES:T: FOR FETCH ONLY
The executed SQL statement will look as follows:
SELECT TABNAME, CREATOR FROM SYSIBM.SYSTABLES WHERE TABNAME > 'SYS' AND CREATOR = 'SYSIBM'
<<ROW CHANGE
TOKEN
FOR
table-designator>>
|
A ROW CHANGE expression returns a token that represents the last change to a
row.
DEFINE DATA LOCAL
01 TEXTVAR (A200)
01 TABLES VIEW OF SYSIBM-SYSTABLES
02 NAME
02 CREATOR
END-DEFINE
*
SELECT << ROW CHANGE TOKEN FOR SYSTABLES >>
INTO TEXTVAR
FROM SYSIBM-SYSTABLES
DISPLAY TEXTVAR
END-SELECT
*
END
|
|
ordered-OLAP-specification |
|
|
|
CUME_DIST ( ) |
|
OVER
([window-partition-clause] window-order-clause)
|
|
PERCENT_RANK ( ) |
||||
RANK ( ) |
||||
DENSE_RANK ( ) |
||||
NTILE (num-tile) |
||||
lag-function |
||||
lead-function |
||||
LAG (
expression [ , offset [
, default [ , |
|
'RESPECT
NULLS' |
|
] ] ] ) |
'IGNORE NULLS' |
LEAD (
expression [ , offset [
, default [ , |
|
'RESPECT
NULLS' |
|
] ] ] ) |
'IGNORE NULLS' |
ROW_NUMBER
( ) OVER ([window-partition-clause] [window-order-clause])
|
|
|
aggregate-function |
|
OVER
( [window-partition-clause])
|
||
| OLAP-column-function | |||||
|
|
RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
|
|||
| window-order-clause |
|
|
|
||
|
|
AVG function |
|
| CORRELATION function | ||
| COUNT function | ||
| COUNT_BIG function | ||
| COVARIANCE function | ||
| MAX function | ||
| MIN function | ||
| STDDEV function | ||
| SUM function | ||
| VARIANCE function |
|
|
first-value-function |
|
| last-value-function | ||
| nth-value-function | ||
| ratio-to-report-function |
FIRST_VALUE (
expression [ , |
|
'RESPECT
NULLS' |
|
] ) |
'IGNORE NULLS' |
LAST_VALUE (
expression [ , |
|
'RESPECT
NULLS' |
|
] ) |
'IGNORE NULLS' |
NTH_VALUE (
expression
|
, nth-row ) |
|
RATIO_TO_REPORT
( expression )
|
|
|
|
|
|
|
|
|
|
|
BETWEEN
group-bound-1
AND
group-bound-2 |
|
|
|
|
|
|
|
|
|
|
|
|
PARTITION BY
partitioning-expression,...
|
ORDER BY
{sort-key-expression |
|
|
ASC |
|
|
},... |
NULLS LAST |
||||||
ASC NULLS FIRST |
||||||
DESC |
||||||
DESC NULLS FIRST |
||||||
DESC NULLS LAST |
Display the ranking of employees that have a total salary of more than $30,000, in order by last name.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, <<RANK() OVER(ORDER BY SALARY+BONUS DESC) AS RANK_SALARY>> FROM DSN8910-EMP WHERE SALARY+BONUS > 30000 ORDER BY LASTNAME;
case-expression
<< CASE
|
|
|
|
ELSE
|
|
|
|
END >>
|
A case-expression does not conform to standard
SQL and is therefore supported by the Natural SQL Extended Set only.
WHEN
search-condition
THEN
|
|
|
A Searched When Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
See details on search-condition.
| scalar-expression |
|
WHEN
scalar-expression
THEN
|
|
 |
|
|
A Simple WHEN Clause does not conform to standard SQL and is therefore
supported by the Natural SQL Extended
Set only.
DEFINE DATA LOCAL 1 VWA VIEW OF NAT-D0001 2 ID 2 NAME 2 CITY 01 #RES1 (A8) 01 #CASE ( I4) INIT<0> END-DEFINE SELECT CITY, << CASE SUBSTR(CITY,1,1) WHEN 'V' THEN 'Administration' WHEN 'D' THEN 'Accounting' WHEN 'K' THEN 'Operations' END >> INTO VWA.CITY , #RES1 FROM NAT-D0001 WRITE VWA.CITY #RES1 END-SELECT END
cast-expression
<<CAST (scalar-expression
AS
data-type) >>
|
A CAST expression does not conform to standard SQL and is therefore
supported by the Natural SQL Extended
Set only.
DEFINE DATA LOCAL 1 VWA VIEW OF NAT-D001 2 ID 2 NAME 2 CITY 01 #RES1 (I4) END-DEFINE SELECT << CAST (ID AS INTEGER) >> INTO #RES1 FROM NAT-D001 WHERE ID = 1 WRITE #RES1 END-SELECT END
XML-Functions
Any available XML functions must be treated with flexible SQL if those functions have
their own specific keyword or syntax, if you are using the AS keyword and
order by statement or any specific statement recognized by SQL. You must place the symbol
of the flexible SQL within that stated portion. Additionally, between the left parathesis
and the left arrow symbol of flexible SQL, you must leave a space or you receive a
compiler error.
DEFINE DATA LOCAL 1 D033412A VIEW OF NATQA-D033412A 2 NAME 2 YEARS_OF_SERVICE 2 ANNUAL_LEAVE 2 TIME_IN 2 BACKGROUND END-DEFINE SELECT XMLSERIALIZE( <<CONTENT XMLELEMENT>>( <<NAME "Annual Leave">>,XMLATTRIBUTES( <<ANNUAL_LEAVE AS "al">>),XMLAGG(XMLELEMENT( <<NAME "name">>,NAME)<<ORDER BY NAME>>) )<<AS CLOB(110)>>) INTO #XMLSERIALIZE FROM NATQA-D033412A GROUP BY ANNUAL_LEAVE END-SELECT END
Scalar-functions and column-functions are only supported with their proper syntax, as stated in the section Scalar Expression. After the function name, within the left and right parentheses between the scalar expressions, there must be a comma. Therefore, not putting a comma between one scalar expression and another is restricted.
Any additional usage of keywords or any SQL statements within the parentheses, which is not recognized as a scalar expression with or without a comma, must be included with the flexible SQL to make it work.
Additionally, between the left parathesis and the left arrow symbol of flexible SQL you must leave a space or you receive a compiler error.
DEFINE DATA LOCAL 01 V1 VIEW OF DSN8910-EMP 02 EMPNO 02 FIRSTNME 02 LASTNAME 02 SALARY 02 BONUS 01 M1 (I4) END-DEFINE M1 := 10000 SELECT * INTO VIEW V1 FROM DSN8910-EMP WHERE SALARY > GREATEST(CAST( <<:M1 AS INTEGER>> )) DISPLAY V1 END-SELECT ENDEND
