Flexible SQL

#### Search Results

Search

# Flexible SQL

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:

- Using Flexible SQL

- Specifying Text Variables in Flexible SQL

- ROW CHANGE Expression with Flexible SQL

- OLAP Specification

- Case Expression with Flexible SQL

- Cast Expression with Flexible SQL

- XML Functions with Flexible SQL

- Scalar-Function and Column-Function (Aggregating) with Flexible SQL

## Using Flexible SQL

In addition to the SQL syntax described in the previous sections, flexible SQL enables you to use arbitrary SQL syntax.

#### Characters `<<` 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:

- `atom`

- `column-reference`

- `scalar-expression`

- `predicate`

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.

#### Example 1

    SELECT NAME
    FROM SQL-EMPLOYEES
    WHERE << MONTH (BIRTH) >>  =  << MONTH (CURRENT_DATE) >>

#### Example 2:

    SELECT NAME
    FROM SQL-EMPLOYEES
    WHERE << MONTH (BIRTH) = MONTH (CURRENT_DATE) >>

#### Example 3:

    SELECT NAME
    FROM SQL-EMPLOYEES
    WHERE SALARY > 50000
    << INTERSECT
       SELECT NAME
       FROM SQL-EMPLOYEES
       WHERE DEPT = 'DEPT10'
    >>

## Specifying Text Variables in Flexible SQL

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 `host-variable` prefixed by `:T:`. It must be in alphanumeric format. 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 `host-variables`. 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 `LINDICATOR` and a length indicator variable (that is, a `host-variable` prefixed by colon). The length indicator variable has to be of format/length I2. If no `LINDICATOR` variable is specified, the entire content of the text variable will be inserted into the SQL string. If you specify a `LINDICATOR` variable, only the first `n` characters (`n` being the value of the `LINDICATOR` variable) of the text variable content will be inserted into the SQL string. If the number in the `LINDICATOR` variable is greater than the length of the text variable content, the entire text variable content will be inserted. If the number in the `LINDICATOR` variable is negative or 0, nothing will be inserted. See general information on `host-variable`.

#### Example Using Text Variable

    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 Expression with Flexible SQL

<<`ROW CHANGE` `TOKEN` `FOR` table-designator>>

A `ROW CHANGE` expression returns a token that represents the last change to a row.

`TOKEN` | Specifies a token of type `BIGINT` that represents a relative point in the modification sequence of a row.
`FOR table-designator` | Identifies the table in which the expression is referenced. `table-designator` has to be a valid Natural SQL DDM.

#### Example Using Row Change Expression with Flexible SQL:

    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

## OLAP Specification

| ordered-OLAP-specification numbering-specification aggregation-specification |

#### 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-function

`LAG` `( expression [ , offset [ , default [ ,` |  | `'RESPECT NULLS'` |  | `] ] ] )`
`'IGNORE NULLS'`

#### lead-function

`LEAD` `( expression [ , offset [ , default [ ,` |  | `'RESPECT NULLS'` |  | `] ] ] )`
`'IGNORE NULLS'`

#### numbering-specification

`ROW_NUMBER` `( ) OVER` ([window-partition-clause] [window-order-clause])

#### aggregation-specification

| aggregate-function |  | `OVER` ( [window-partition-clause])
OLAP-column-function
| `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` |
window-order-clause |  | `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` window-aggregation-group-clause |

#### aggregate-function

| AVG function |
CORRELATION function
COUNT function
COUNT_BIG function
COVARIANCE function
MAX function
MIN function
STDDEV function
SUM function
VARIANCE function

#### OLAP-column-function

| first-value-function |
last-value-function
nth-value-function
ratio-to-report-function

#### first-value-function

`FIRST_VALUE` `( expression [ ,` |  | `'RESPECT NULLS'` |  | `] )`
`'IGNORE NULLS'`

#### last-value-function

`LAST_VALUE` `( expression [ ,` |  | `'RESPECT NULLS'` |  | `] )`
`'IGNORE NULLS'`

#### nth-value-function

`NTH_VALUE` `( expression` | `, nth-row )`

#### ratio-to-report-function

`RATIO_TO_REPORT` `( expression )`

#### window-aggregation-group-clause

| `ROWS` `RANGE` |  |  | group-start group-between group-end |

#### group-start

| `UNBOUNDED PRECEDING` unsigned-constant `PRECEDING` `CURRENT ROW` |

#### group-between

`BETWEEN` group-bound-1 `AND` group-bound-2

#### group-bound-1

| `UNBOUNDED PRECEDING` unsigned-constant `PRECEDING` unsigned-constant `FOLLOWING` `CURRENT ROW` |

#### group-bound-2

| `UNBOUNDED FOLLOWING` unsigned-constant `PRECEDING` unsigned-constant `FOLLOWING` `CURRENT ROW` |

#### group-end

| `UNBOUNDED FOLLOWING` unsigned-constant `FOLLOWING` |

#### window-partition-clause

`PARTITION BY` partitioning-expression,...

#### window-order-clause

`ORDER BY` {sort-key-expression |  |  | `ASC` |  |  | },...
`NULLS LAST`
`ASC NULLS FIRST`
`DESC`
`DESC NULLS FIRST`
`DESC NULLS LAST`

`RANK` | Specifies that the rank of a row is defined as 1 plus the number of rows that strictly precede the row.
`DENSE_RANK` | Specifies that the rank of a row is defined as 1 plus the number of preceding rows that are distinct with respect to the ordering.
`ROW_NUMBER` | Specifies that a sequential row number is computed for the row that is defined by the ordering, starting with 1 for the first row.
`PARTITION BY` | Defines the partition within which the OLAP operation is applied.
`ORDER BY` | Defines the ordering of rows within a partition that is used to determine the value of the OLAP specification.
`ASC` | Specifies that the values of `sort-key-expression` are used in ascending order.
`DESC` | Specifies that the values of `sort-key-expression` are used in descending order.
`NULLS_FIRST` | Specifies that the window ordering considers null values before all non-null values in the sort order.
`NULLS LAST` | Specifies that the window ordering considers null values after all non-null values in the sort order.

#### Example:

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 with Flexible SQL

`case-expression`

<< `CASE` |  | searched-when-clause simple-when-clause |  |  | `ELSE` |  | `NULL` scalar-expression |  |  | `END` >>

A `case-expression` does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

### Searched WHEN Clause

`WHEN` search-condition `THEN` |  | `NULL` scalar-expression |

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

### Simple WHEN Clause

scalar-expression |  | `WHEN` scalar-expression `THEN` |  | `NULL` scalar-expression |  |  |

A Simple `WHEN` Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

#### Example:

    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 with Flexible SQL

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.

#### Example:

    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 with Flexible SQL

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.

#### Example:

    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-Function and Column-Function (Aggregating) with Flexible SQL

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.

#### Example:

    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

### On this page

- Using Flexible SQL
- Specifying Text Variables in Flexible SQL
- ROW CHANGE Expression with Flexible SQL
- OLAP Specification
- Case Expression with Flexible SQL
- Cast Expression with Flexible SQL
- XML Functions with Flexible SQL
- Scalar-Function and Column-Function (Aggregating) with Flexible SQL