Mitsubishi Electric MELSEC iQ-R-R04CPU - Clause

1000
APPX
Appendix 14 List of Available SQL Commands for CPU Module Database Access Function
Clause
WHERE
This command conducts search with conditions.
■Operators that can be used in [condition]
■Application example
• To output a record with the fld1 value of 1 to 100 of table1
SELECT * FROM "table1" WHERE "fld1" BETWEEN 1 AND 100;
• To output fld1 and fld3 of the record with the fld3 value other than abc of table1
SELECT "fld1", "fld3" FROM "table1" WHERE NOT "fld3" = 'abc';
GROUP BY
This command groups elements.
■Application example
• To group Table1 according to the value of fld1 and aggregate the number of records
SELECT COUNT(*) FROM "table1" GROUP BY "fld1";
HAVING
This command narrows down a search using the result of the aggregate function.
■Application example
• To output only the groups whose total value is 50 or less
SELECT SUM ("fld2") FROM "table1" GROUP BY "fld1" HAVING SUM ("fld2") < 50;
Syntax
• To specify a condition
WHERE [condition]
• To specify an exception
WHERE NOT [condition];
• To specify two conditions with AND
WHERE [condition 1] AND [condition 2];
• To specify two conditions with OR
WHERE [condition 1] OR [condition 2];
Item Description Syntax
= Equals [numeric expression 1] = [numeric expression 2]
!= Not equal to [numeric expression 1] != [numeric expression 2]
<> Not equal to [numeric expression 1] <> [numeric expression 2]
> Greater than [numeric expression 1] > [numeric expression 2]
>= Greater than or equal to [numeric expression 1] >= [numeric expression 2]
< Less than [numeric expression 1] < [numeric expression 2]
<= Less than or equal to [numeric expression 1] <= [numeric expression 2]
IN Brings the conditions to be set together. IN ([numeric expression 1], [numeric expression 2], , [numeric
expression 3])
BETWEEN Specifies the range to be output. [numeric expression 1] BETWEEN [numeric expression 2] AND
[numeric expression 3]
Syntax
GROUP BY [numeric expression]
Syntax
HAVING [numeric expression]