Skip to main content

Creating a Query

Creating an SQL Statement or Query

You can create your SQL Statement using the Interactive SQL or the Embedded SQL Report facilities in roomMaster. The Interactive SQL Report feature allows certain users to create and maintain reports. An Embedded SQL Report can be created by higher access users and saved to be run by multiple users. The Interactive SQL option is accessed by going to Tools & Utilities > Interactive SQL Report. For the Embedded SQL Report, go to Reports > Custom Reports > Properties button > Insert > SQL Statement. In either, the SQL Report window appears. Build your SQL Queries in the free form field using the table tree and the built in features.

The Table List displays all the tables and views available in a tree format. Double click on a table or view to display the fields included in the table. Different icons are used to identify fields that contain text, numbers, numbers that include decimals, selections, and dates and times. Use these icons as a guide when determining the type of data contained in the field. Check Show Field Descriptions to display descriptions of each field. The description in the parenthesis gives information about the data contained in the field:

  • No Parenthesis - The field contains text data. This information can be words or numbers, but is not information that cannot be calculated. For example, a room number is numeric, but it is not a number you would use in a calculation. You would not need to know the total of all room numbers that stayed for a specific night.

  • Number - The field contains a whole number that can be calculated. For example, the number of Adults or Children that stayed over a time frame could be determined. It is a whole number since there would not be "half" or ".75" Adults in a room.

  • Decimal - The field contains a number that includes a decimal, such as rates. These fields can be calculated.

  • Date and Time - These fields are in Date and Time format, like Checkin date or time of day a reservation was entered.

  • Tiny - These fields contain data that has a finite number of options or has to be selected. For example, PHONERATE, the EPI phone rate on a folio can only be 1, 2 or 3, so it is considered a "tiny" field. OVERRATE, Override rate, is also a tiny field since it has to be selected to be on, or unselected to be off.

The Lookup Table Columns (F7) button displays available tables inside the roomMaster database.

Insert Prompt Field (F8) allows you to create Prompt for Value fields which prompt the user when the report is executed. Simply type any statement you wish to display to the user, and the information entered will be inserted directly into the SQL Statement.

Execute SQL Statement (F9) Run Query allows you to execute/test your SQL statement and view the results immediately in the Data Grid Preview.

Previous/Next Statement Arrows allow you to recall SQL statements created since you have logged on to roomMaster. When you sign off (Close down or Alt-F3), the history of these statements are released specifically for security purposes, so no other user can recall or view your SQL statements.

CleanSQL (CTRL+K) attempts to format the query to a standard SQL statement for easier reading. To undo the formatting, select CTRL+Z (undo).

If you are not as familiar with the writing SQL Queries, use the SQL Wizard to guide you through creating statements. Click here for more information on using the SQL Wizard.

Use the Label Wizard to quickly create a statement that will print properly formatted address labels.

tip

Make sure to select a table that contains information required for an address label, i.e. use the Label Wizard on RESERVE because guests names and addresses are included in the table. Do not select ROOMBOOK because it only contains information about rooms assigned. No guest data is included in this table.

Automatic Pop-Up of Tables and Column Names

When writing a free-form query, you can at any time press CTRL+SPACE to bring up a list of table names. Once the table names appear, you can find the table you want and press CTRL+SPACE again to bring up the columns related to that table. Once you find the column you want, simply select Enter on it to bring it into the query. If you press Enter on a table name, the table name is inserted into the query rather than advancing to the related columns.

  • Pressing F10 while the table or column names appear will toggle a sort (by field index or alphabetical)

  • Typing the table name followed by a period (ie: FOLIOHD.) and then pressing CTRL+SPACE will immediately bring up the related columns for that table.

  • Pressing F9 or SpaceBar on columns after selecting a table will select multiple columns.