Condition Setting
This page describes the “condition” that are set when creating a query in "query" .
Contents
Condition setting
"Condition" is setting of narrowing down all records in the table.
For No.1 select box, select the column name you want to specify in the condition.
For No.2 select box, set the operator to be used. The following eight operators can be selected. [＝], [>], [<], [>=], [<=], [<>], [like], and [in].
[＝] indicates equal to '==', [>] indicates greater than, [<] indicates smaller than, [>=] indicates equal to or greater than, [<=] indicates equal to or smaller than, [<>] indicates not equal to "!=".
[like] is used for pattern matching, and [in] is used for subquery. See Complex Conditions for details.
For No.3 select box, select the type of value you want to specify for the condition. The input area in No. 4 changes depending on the selected type.
For the value type, "Fixed value" that encloses the value input in No.4 with single quotation ' ', 'Variable' using the value of the device and the value of the HMI variable, 'Time specification' for time, "SQL statement" that encloses the input value with parentheses (), and "isNULL" that specifies NULL.
When selecting "isNULL", if you choose anything other than "=" in the No. 2 input area are treated as notNULL.
Refer to the Complex Conditions for detailed setting when you select "Time specification ".
In the No.4 input area, set the value specified for the condition.
If choosing anything other than "variable" in No. 3, the variable name will be decided as a string even the variable name is directly entered in No. 4.
When "Fixed value" is selected in No. 3, avoid using letters that are unsuitable for SQL statements such as 'single quotation' or 'double quotation' for values.
Also, when "SQL statement" is selected in No. 3, extra care is required as the input area in No. 4 has the highest degree of freedom.
Refer to subquery of postgreSQL when using "SQL statement".
In the No.5 select box, AND clause or OR clause can be selected. It does not exist in the bottom column.
Complex Conditions
Here described some more complex conditions.
LIKE (LIKE operator)
An operator that can be selected from No. 2 and performs pattern matching for the value. Basically, the fixed value in No.3. is used.
A simple example is listed below.
Statement Input value in No.3 Result 'WEB' LIKE 'WEB' WEB true 'WEB' LIKE 'E' E false 'WEB' LIKE 'W%' W% true 'WEB' LIKE 'WE%B' WE%B true 'WEB' LIKE '_E_' _E_ true 'WEB' LIKE 'WE_B' WE_B false
The table above shows the results of the pattern matching on a string 'WEB'.
If percent sign (%) or underscore (_) is not included, the same as equal to [=] is executed.
(_) indicates a match with one arbitrary letter, and (%) indicates a match with a sequence of 0 or more letters.
Using LIKE operator to search timestamp is not recommended, such as “_timestamp_ LIKE '1997-07-26%'”, though is can be used, but the execution speed is slow.
IN (IN operator)
An operator that can be selected from No. 2 and makes a subquery for the value. Basically, the SQL statement in No.3. is used./p>
Using IN requires knowledge of SQL.
If AND and OR are mixed and require numerous conditions, you may be able to simplify condition setting by using the IN operator.
An example is listed below.
When selecting the record with the value of column name a (hereinafter, column a) is 20 or larger, and the value of column b is 1, 5 or 10, The setting of this condition without using IN is listed below.
Conditional statement to be created: a >= '20' and b = '1' or a >= '20' and b = '5' or a >= '20' and b = '10'
With using IN, the setting of this condition is listed below.
Conditional statement to be created: a <= '20' and b in( '1' , '5' , '10' );
Using IN operator reduces the number of conditions to be created, and this decreases the necessary work as a result.
The select statement can be entered for the input value, but the input values are not checked on the executing side. When executing the query, therefore, a serious error may occur in some cases.
Using the select statements is not recommended for this reason.
Time specification
When “Time Specification” is selected in No.3, three types of input area are provided in the No.4 depending on the state of the top select box.
The types vary depending on selecting “Now”, or “Today”, “Last week” , “Last Month Day 1”, and selecting the ”Fixed time”.
The part surrounded by the red color sets the elapsed time from the selected time when selecting "Now", "Today", "Last Week", "Last Month Day 1".
Enter – to make it as the past.
The part surrounded by the blue color sets the time of the selected time when selecting "Today", "Last Week", "Last Month Day 1".
The part surrounded by green sets the fixed time when "fixed time" is selected.
Condition Creating
Here described some condition specifications.
Set a range
When specifying a range or a period such as "between 1 and 10", "yesterday through today", use ">", "<", ">=", and "<=" to express as follows:
Overwrite
Specify the condition as the same as the column name of "Write Data" when updating, this overwrites the value suited to the specified condition.
To overwrite a value 1 of the record to -1A query example: update dbo.example_table set a = '-1' where a = '１';
