Skip to main content

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.

[ [ 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.