SQL Where Clause

PROIV Documentation

Click here to see this page in full context

Getting started with the SQL Where Clause

This feature is accessible when editing a function. You can find it under a dedicated tab labelled "SQL Select" within the file properties window. Please note that the availability of this tab is contingent upon three critical conditions: firstly, the selected file must belong to the SQL file type category, secondly, the associated cycle must be classified as a 'many-time' cycle, and thirdly, the VFA (Virtual File Accessor) is mutually exclusive and this tab is disabled.

Timing Cycle

There shall be no influence on the timing cycle, nor will there be any alteration to the sequence in which logic event points are processed.

Transparent SQL

The 'Where Clause' feature is an enhancement (not replacement) to the existing 'Transparent SQL' mechanism generated by PROIV when referencing SQL tables.

Full function SQL (Embedded SQL)

The 'Where Clause' feature and 'full functional SQL' are mutually exclusive. Consequently any attempt to use them concurrently will lead to a build time error.

Secondary Files

Embedded SQL takes precedence over all the criteria established by transparent SQL for secondary file keys. It is the Embedded SQL that governs the method by which rows are chosen from the secondary file.

The introduction of a 'Where Clause' on a secondary file takes precedence over all the criteria set by transparent SQL on an individual key basis. If the 'Where Clause' imposes constraints on a specific secondary file key, the standard transparent SQL criteria entailing the matching of the key to the file variable is deliberately omitted. Conversely, keys lacking constraints within the 'Where Clause' default to the transparent SQL criteria.

Where Clause Operators

The key where clause operators are:

Operator	Description
Blank	The primary line signifies the onset of the 'Where' SQL segment.
AND	Used to combine multiple conditions in a SQL statement.
OR	Used to combine multiple conditions in a SQL statement. It ensures that at least one of the specified conditions is met for a record to be included in the result set.

SQL Where Clause Statement Configuration

The initial line of the 'Where' statement mandates that the 'And/Or' field remains unset, inherently denoting it as the commencement of the 'Where' SQL segment. It is mandatory to populate the Column Name, Operator, and Value fields. While the inclusion of open and close parenthesis remains discretionary, they can be utilised to enhance clarity and structure.

Valid field entries in this tab are:

Field Name	Value
A	Click 'green cross' icon to add a line.
D	Click the 'red cross' icon to delete selected line.
And/Or	Operator. Drop down window selection available.
(	Opening parenthesis used to start the condition group.
Column Name	Column Name from the SQL type file accessor. Drop down window selection available.
Operator	Operator - see Operator table below. Drop down window selection available.
Value	Value - see Value table below. Can be selected from the variable selection window.
)	Closing parenthesis used to end the condition group.
'Add' Button	Adds a new line to the 'Where Clause'.
'Delete' Button	Removes the current selected line from the 'Where Clause'.
'Delete All' Button	Removes all entries from the 'Where Clause'.

Valid entries for each field And/Or:

Operator	Description
Blank	Indicates the start of the where clause and can only be blank for the first line.
And	SQL 'And' condition (optional) for all subsequent lines following the initial line.
Or	SQL 'Or' condition (optional) for all subsequent lines following the initial line.

For each ( field, valid entries are:

Value	Description
Blank	No opening of a condition group.
(	Opening of a condition group (optional). Must have a matching closing parenthesis.

For each Column Name, valid entries are:

Column Name	Description
File Variable Name	Any valid file variable from the selected file accessor (mandatory).

For each operator then the field is mandatory, valid entries are:

Operator	Description
=	Equal to
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
IS NULL	Column is null
IS NOT NULL	Column is not null
LIKE	Operator is used in a where clause to search for a specified pattern in a column
NOT LIKE	Operator is used in a where clause to search for a specified pattern not in a column

For each value, valid entries are:

Value Name	Description
Scratch Variables	$$, $, ##, # etc available to the current function
Scratch Variables Arrays	$$(), $(), ##(), #() etc available to the current function - where '*' refers to any numeric number from the start element of the array to the end element of the array
Value Variables	&$ and &#
System Variables	@DATE, @TIME, @TERM etc
Communication Variables	@$COM1-10 and @#COM1-10
Literal String Value	'Value', "Value"
Literal Numeric Value	Any numeric value
File Variable	Any file variable available to the current function including arrays, where arrays are defined as for scratch variable arrays

For each ) field, valid entries are:

Value	Description
Blank	No closing of a condition group.
)	Closing of a condition group (optional). Must have a matching opening parenthesis.