Appendix A
A-37
A
NJ/NX-series Database Connection CPU Units User’s Manual (W527)
When using single quotes in the WHERE and SORT clauses, use the escape character ($’).
Refer to the NJ/NX-series CPU Unit Software User’s Manual (Cat. No. W501) for the escape character.
Refer to the manual of the database for the format of the WHERE and SORT clauses.
Specify the retrieval conditions by the following values in the Where input variable.
Example 1: Retrieve the values of the records where the value of a specific column is equal to or greater than
the specified value.
Retrieve the values of records where the value of “ColumnA” (unsigned integer) is 1234 or greater.
'“ColumnA” >= 1234'
SQL statement to create: SELECT FROM TableProduct Where “ColumnA” = 1234
Example 2: Retrieve the records where the values of specific two columns are within the specified range.
Retrieve the records where the value of “ColumnA” (unsigned integer) is bigger than 1000 and the value of
“ColumnB” (unsigned integer) is smaller than 2000.
'“ColumnA” > 1000 AND “ColumnB” < 2000'
SQL statement to create: SELECT FROM TableProduct Where “ColumnA” > 1000 AND “ColumnB” < 2000
Example 3: Retrieve the values of the records where the value of a specific column is equal to or greater than
the value of the specified variable.
Retrieve the values of records where the value of “ColumnA” (unsigned integer) is equal to or greater than
the specified variable.
Specified value: UINTVar := 1234;
Input parameter in the WHERE clause:
WhereCond_Select := CONCAT(‘$”ColumnA$” >= ‘, UINT_TO_STRING(UINTVar));
SQL statement to create: SELECT FROM TableProduct Where “ColumnA” >= 1234
Specify the sort conditions in the Sort input variable by the following values.
Example: Retrieve the records sorted by the values of two columns.
Sort the values of “ColumnA” in ascending order and values of “ColumnB” in descending order.
'“ColumnA” ASC, “ColumnB” DESC'
SQL statement to create: SELECT FROM TableProduct ORDER BY “ColumnA” ASC, “ColumnB” DESC
Precautions for Correct Use
Execution of this instruction is continued until processing is completed even if the value of Execute changes to
FALSE or the execution time exceeds the task period. The value of Done changes to TRUE when processing
is completed. Use this to confirm normal completion of processing.
Refer to Using this Section of the NJ/NX-series Instructions Reference Manual (Cat. No. W502) for a timing
chart for Execute, Done, Busy, and Error.
This instruction cannot be used on an event task. A compiling error will occur.
This instruction cannot be executed without specifying the retrieval conditions.
When no record is retrieved as the execution result of this instruction, the values of the RecCnt and
SelectedCnt output variables are both 0 and the instruction is normally completed.
Even if the number of array elements of the DB Map Variable does not match the number of retrieved records
as the execution result of this instruction, the instruction is also normally completed.
When the DB Connection Service was started in Test Mode, this instruction is normally ended without
executing the SELECT operation for the DB actually. No values are stored in the DB Map Variable specified in
the MapVar in-out variable and 0 is output to both the RecCnt and SelectedCnt output variables.
Even if the specified number of bytes in STRING data is shorter than the table data, this instruction is normally
ended.
Example: When 12 characters are contained in a table column and data type of the corresponding member of
To Next Page
Loading...
