Getting started with the SQL Where Clause

Trail: PROIV Documentation > Developer > PROIV Developer > Developing Functions > SQL Where Clause

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 two critical conditions: firstly, the selected file must belong to the SQL file type category, and secondly, the associated cycle must be classified as a 'many-time' cycle.

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:

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.

1. 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'.

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

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

4. For each Column Name, valid entries are:

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

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

6. For each value, valid entries are:

   Value Name	Description
   Scratch Variables	$$, $, ##, # etc avaiable to the current function
   Scratch Variables Arrays	$$(*), $(*), ##(*), #(*) etc avaiable to the current function - where '*' refers to any numric 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

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

    

   Related Topics

   Document Function File Accessor
   Report Function File Accessor
   Screen Function File Accessor
   Update Function File Accessor
   SQL Where Clause behaviour on secondary file(s)

   Comment on this topic

   Topic ID: 520201

   ---