Getting started with SQL Where Clause
|
Getting started with SQL Where Clause |
|
Key Concepts of SQL Where Clause
You can create a new configuration where you need to deploy a where clause against SQL type files. A tab is available when selecting file properties. The tab is disabled if the file type of the file selected is not type SQL and/or if the cycle to which the file is attached are 'one time' cycles. If the cycle is 'many time' and contains an SQL type file then the tab is enabled and will allow entry of where clause SQL statements.
Timing Cycle
There will be no impact on the timing cycle and the order of which logic events points are processed.
When a "Where Clause" has been specified in a cycle the order of processing will continue as it does today, the only expectation is the "Where Clause" select takes precedence over any other selection being defined in the "On Entry" logic, for example:
Secondary Files
When a 'Where Clause' is specified on a secondary file read, the query will be executed, returning solely the initial row identified. Subsequently, the 'After Read No Error' logic will be processed for that specific row. In the event that the query yields no results, the 'On Error' logic will be processed in alignment with conventional timing cycle procedures.
Where Clause Operators
The key where clause operators are:
Operator |
Description |
WHERE | Used to extract only those records that fulfil a specified condition and can only be specified once |
AND | True if all the conditions separated by AND is true |
OR | True if any of the conditions separated by OR is true |
SQL Where Clause Statement Configuration
When the 'SQL Select' tab is enabled then SQL where clause statement line(s) can be entered.
The first line of the where statement must have 'And/Or' set to blank and is recognised, by default, a 'Where' statement, and indicates the start of the 'Where' SQL details. The Column Name, Operator and Value fields are mandatory. Open and close brackets are optional.
Subsequent lines of the where statement must have 'And/Or' set to And or OR which indicates each 'Where' SQL detail line. The Column Name, Operator and Value fields are mandatory. Open and close brackets are optional.
If an open bracket is entered then a corresponding closing bracket is mandatory. Mismatching bracket sets will give a GEN error in the kernel.
Valid field entries in this tab are:
Field Name |
Value |
A | Click green cross to add a line |
D | Click red cross to delete selected line(s) |
And/Or | Conditions. Drop down window selection available |
( | Open bracket to nest where clause statements |
Column Name | Field from the SQL type file selected. 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 |
) | Close bracket to ended nested where clause statements |
'Add' Button | To add a line |
'Delete' Button | To delete selected line(s) |
'Delete All' Button | To delete all line(s) |
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 - mandatory for subsequent lines after line 1 |
Or | SQL 'Or' condition - mandatory for subsequent lines after line 1 |
For each ( field, valid entries are:
Value |
Description |
Blank | No SQL where statement nesting |
( | Start of SQL where statement nesting - must have a matching closing bracket - optional |
For each Column Name, valid entries are:
Column Name |
Description |
File Variable Name | Any valid file variable from the selected file - 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 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 |
Blank | Indicates a null 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 SQL where statement nesting |
) | Start of SQL where statement nesting - must have a matching opening bracket |
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)
Topic ID: 520201