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.
Flexible SQL is enclosed in
>> characters. It can include arbitrary SQL text and host
variables. Within flexible SQL, host variables must be prefixed by a
The flexible SQL string can cover several statement lines. Comments
are possible, too (see also the statement
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 ... << ... >>
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".
The syntax items are described below:
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.
The text variable can be followed by the keyword
The length indicator variable has to be of format/length I2.
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 (as displayed with the
LISTSQL system command) 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'