Entire SQL Syntax

The following is the entire SQL Syntax: * The InnQuest (roomMaster) Extension Functions are shaded with grey background

Lines in single brackets [ ] can only appear once, where-as lines in multiple breaks [ [ ] ] can be repeated.

```SQL {64-72} [ [ SETVARIABLE name=value ] repeated as needed ]

SELECT [ ALL | DISTINCT ] [ row-limitation ] column as 'column-title', ...

[ FROM table-expression ] [ WHERE search-condition ] [ GROUP BY group-by-expression ] [ HAVING search-condition ] [ ORDER BY { expression | integer } [ ASC | DESC ], ... ]

[ GO; ] [GO SKIP_ERROR;]

[ SUBTOTAL {column | AVG(column) | COUNT(column) | ACOUNT(column) | WAVG(columnlist) , MIN(column), MAX(column), ... }

[ON break-column | EXISTS(column) | MONTH(column) | YEAR(column) ,... ] [HIDEDETAIL] [HIDEGRANDTOTAL]

[ PIVOTRESULTSET [WITHTOTALS] ]

[ PIVOTRESULTFIELDS ColumnTitle,ColumnTitle,... ]

[ DOMAINCHECK ON email-column [ONLYINVALID | ONLYVALID] ]

[ PAGEBREAK ON [ break-column ,... ]

[ [ HIGHLIGHT column [TO column] WHERE valid evaluate statement [BGCOLOR=nnnnnnn] [IGNORETOTALS] ] repeated as needed ]

[ [ FORMATCOLUMN column [TO column],ATTRIBUTE=VALUE ] repeated as needed ]

[ SETREPORTHEADER 'report header string text 1-255 characters' ]

[ SETREPORTFILTER 'report filter override string text 1-255 characters' ]

[ SETREPORTFOOTER 'report footer string text 1-255 characters' ]

[ SETREPORTPAPER 'LETTER | LEGAL | A4' ]

[ NOREPORTFILTER ]

[ SETREPORTFONTNAME fontname ]

[ SETREPORTFONTSIZE fontsize ]

[ SETREPORTFONTADJUST column resize adjustment factor ]

[ FORMATSPACING SINGLE | 1.5 | DOUBLE | TIGHT | fixed line height in DLU ]

[ ALWAYSLANDSCAPE ]

[ FORCEGROUPHEADER {YES | NO} ]

[ FORCEDATEFORMAT 'date_picture' ]

[ NODATAGRIDPREVIEW ]

[ NOMAXDISPLAY ]

[ NORECORDSCOUNT ]

[ NOCURRENCYPICTURE ]

[ EXPORT {AS FILE | AS GRAPH} USING {‘name of .qwe file’ | ‘ValidChartType’} [LABELFORMAT=valid label format such as {M} or {V} or {A}:{V}] ]

[ [ RESERVETOTALSTAY (CONF # column, total type) ] repeated as needed ]

[ [ FOLIOTOTALSTAY (FOLIO # column, total type) ] repeated as needed ]

[ [ RATETOTALSTAY (RATE TYPE column, Room Type, Date, Adults, Children, Number of Rooms) ] repeated as needed ]

[ [ FOREIGNCURRENCY (money column, 'CurrencyCode') ] repeated as needed ]

[ [ CREATEFIELD fieldname=valid evaluate statement ] repeated as needed ]

```

:::important All ISQL and InnQuest Extension Functions must appear on a line by themselves, in the order above when used. :::

More information on functions:

• SETVARIABLE - Creates variables to be used through-out the SQL statement
• SUBTOTAL - Creates Sub-Totals and Grand Totals on Columns.
• PIVOTRESULTSET - Changes the displayed results in to a Cross tab
• HIGHLIGHT - Conditionally highlights a column
• FORMATCOLUMN - Overrides the fields attributes on a printed report.
• EXPORT USING - Automatically executes the Export function when the query is run.
• DOMAINCHECK - Checks the domain name portion (DNS Lookup) for validity on a valid Email address.
• PAGEBREAK ON - Forces a new page when printing a report when the field in that column changes to another value.
• ALWAYSLANDSCAPE - When printing a report, forces the layout to be landscape regardless of number of columns.
• FORCEGROUPHEADER - Forces the first column to group together regardless of Auto Group Header being turned on.
• FORCEDATEFORMAT - Forces all dates to use this picture (ie: @d011- or @d017 or @d02) rather than what the Workstation setting is set to (ie: Convert dates to Windows format)
• SETREPORTHEADER - When printing a report, this text string will appear at the top of the report.
• SETREPORTFILTER - When printing a report, this text string will appear in place of any filter text the system automatically creates for prompt fields.
• SETREPORTFOOTER - When printing a report, this text string will appear at the bottom right of the report.
• NOREPORTFILTER - Stops the report filter header string from appearing in printed report when there are Prompt Fields.
• SETREPORTFONTNAME - Sets the generic/common font name to use for all bands in the report (default=Arial)
• SETREPORTFONTSIZE - Sets the generic/common font size to use for all bands in report (default=10)
• SETREPORTFONTADJUST - Sets the adjustment factor to resize all printed columns when changing the font size (default=20 pixels)
• FORMATSPACING - Sets the detail line height when printing report
• NODATAGRIDPREVIEW - Forces the report to be generated skipping the data grid display.
• NOMAXDISPLAY - Ignore workstations setting for "Max rows to display" - used usually for Export Queries that auto-run.
• NOCURRENCYPICTURE - Forces that no currency picture is used for DECIMAL(13,2) fields.
• HIDEDETAIL - Hides the detail rows of a query. Only subtotal rows and grand totals are displayed.
• HIDEGRANDTOTAL - Hides the grand total of a query.
• RESERVETOTALSTAY - Changes a Confirmation Number column to a reservation total stay value.
• FOLIOTOTALSTAY - Changes a Folio Number column to a reservation total stay value.
• RATETOTALSTAY - Changes a Rate Code to the actual rate.
• FOREIGNCURRENCY - Changes a monetary value to a foreign currency amount.
• CREATEFIELD - Create a runtime field using Clarion Syntax against all existing rows.