QUERY function SQL Syntax - j5 - 30 - Administration & Configuration - Hexagon

j5 IndustraForm Designer Reference

Language
English
Product
j5
Search by Category
Administration & Configuration
j5 Version
30

The first parameter to the QUERY function is an SQL query. A subset of the ANSI SQL SELECT statement is supported with one important variation - instead of table names (for example, in the FROM clause), a question mark parameter marker is used. An import function should then be used to provide the data for that virtual "table" in the query.

As is ANSI standard, there needs to be a question mark for each parameter. For example, WHERE parentlogid IN (?, ?, ?)", Parameter01, Parameter02, Parameter03).

The following SELECT syntax keywords are supported:

  • SELECT

  • DISTINCT

  • TOP (but not TOP PERCENT)

  • AS (providing aliases for column names or virtual tables)

  • FROM (including subqueries)

  • [INNER] JOIN

  • LEFT JOIN

  • ON

  • WHERE

  • EXISTS

  • ORDER BY

  • DESC / ASC

  • GROUP BY

  • HAVING

  • UNION ALL

The following operators are supported for expressions:

  • NOT AND

  • OR

  • =, >, >=, <, <=, <>, !=

  • IS

  • IN

  • LIKE (however only prefix searches are allowed, for example, "MySearch%")

  • BETWEEN

  • +, -, *, /

The following SQL aggregate functions are supported:

  • SUM

  • COUNT

  • MIN

  • MAX

  • AVG

The following SQL scalar functions are supported:

  • CONCAT

  • CONCAT_WS

  • REPLACE

  • COALESCE

  • ABS

  • EXP

  • IIF

  • LEN

  • LOWER

  • LTRIM

  • RTRIM

  • SUBSTRING

  • ISNULL

  • POWER

  • ROUND

  • CEILING

  • FLOOR

  • SQRT

  • TRIM

  • UPPER

In addition, the following custom j5 scalar functions can be used to convert between SQL Date/Time values and Spreadsheet Date/Time serial numbers:

  • N

  • D