SQL Language Support
Enumerating all of the rules and nuances of SQL is outside the scope of this document. You can get very helpful tutorials on SQL from www.w3schools.com/sql/. FlexSim's SQL parser supports a relatively small subset of SQL language constructs, but hopefully it is enough for most of our users' needs. We list here the full set of SQL constructs supported by FlexSim's SQL parser:
- SELECT
- FROM with inner join using commas and $ syntax to define tables explicitly
- WHERE
-
- IN
- BETWEEN
- AND/OR
- LIKE
- ORDER BY
-
- with multiple comma-delimited criteria
- ASC, DESC options
- LIMIT
- SQL Aliases using AS
- SQL Aggregation Functions
- SUM
- AVG
- COUNT
- MIN
- MAX
- STD
- VAR
- GROUP BY
SQL Query Result Retrieval Commands
Below are the set of commands you can use in querying and getting query results
dumpquery(node destTable, num asTable) - Dumps the full result table for the last call to query() into a node. If asTable is 1, it will dump the result as table data. If asTable is 0, it will dump it as bundle data.
getquerymatchcount() - Returns the total number of row in the result table for the last call to query().
getquerycolcount() - Returns the number of columns in the result table for the last call to query().
getqueryvalue(num row, num/str col) - Returns a value in the result table for the last call to query().
row - The 1-based row number of the result table
col - The 1-based column number of the result table, or the name of the result column. If you have defined aliases in the select statement, the column name should be the alias.getquerymatchtablerow(num/str table, num matchRow) - Returns the source table's row number associated with a matched row in the last query() result.
table - If a string, it is the name of the table as defined in the FROM statement. If you used an alias, it should be the table's alias. If a number, it should be the 1-based index of the table in the FROM statement. If there is only one table in the query, this should be 1. For multiple tables, the first table in the FROM is 1, the second is 2, etc.
matchRow - The 1-based row of the result table of the query.
