SQL - Creating Queries that Always Work

One of the things we notice when people write queries is they "hard-code" a lot of information. Hard-code means that you specifically include the "selection information" in your query rather than going out to a sub-table to build the list dynamically.

Sure, the query works, but what about six months later when this information changes. Will your query now be affected and stop working properly?

Simple example:

Let's say you are listing all the ROOM REVENUE transactions that occurred today and you write this query:

SELECT TRANDATE,TRANTYPE,DEBIT,CREDIT,VOUCHER FROM JOURNAL WHERE TRANTYPE = 100 OR TRANTYPE = 101

Great ... you run it, it produces what you want, the query is placed on the Reports menu and everyone is happy.

Six months later or a year later, someone adds a new Room Revenue account (Code: 102) for some new building or function room and no one tells you or you forget about your query. Your query is still in production but is now missing account code 102. Finally, someone realizes it and you modify your query to say "OR TRANTYPE = 102"

When you use the roomMaster configuration information, you are able to make this work all the time without hard-coding, if you know where to look. If you know roomMaster, you know that every time you enter a new account code (Setup > Configuration > Guest Ledger > Account Codes), you enter a code, and also designate a Report Group, Report Sub-Group, Account Type, Routing information and more. So, why not use that in your query instead so when the query runs, it simply pulls all the account types which are "Room Revenue". Make sense?

SELECT Trandate, Trantype, Debit, Credit, Voucher FROM JOURNAL WHERE trantype in (select accountcode from accounts where acctype = 'Room Revenue')

So, in this example, instead of hard-coding accounts 100, 101 and 102, the system simply builds a list of all the account codes from the ACCOUNTS table that are ROOM REVENUE accounts and the main query selects off that list. Now, no matter how many times the account codes change, your query always works.

Another example would be in the iQ-POS. Instead of hard-coding ITEMS, you can use the RM Account Code, the Category, the Vendor or anything else that will group those items together. Such as, if all the items are "APPETIZERS" and those are part of category "CC".

One more example would be listing all rooming information from the forecast file, but excluding meeting rooms, or even on an arrivals list. There is a view called "VIEW_ROOMTYPES" which lists all the room types, the Room Group and whether or not it is a Meeting Room.

So, instead of:

SELECT * FROM forecast WHERE bedtype <> 'MTG'

you can do:

SELECT * FROM forecast

WHERE bedtype in (SELECT ROOMTYPE FROM view_roomtypes WHERE VIEW_ROOMTYPES.MTGROOM='0')

This only pulls up room types which are not meeting rooms. You can modify it just as easy if you wanted only Room Group 1 versus Room Group 2, because you wanted say Building 1 versus Building 2.

Always try and avoid hard-coding information into your query and if there is a sub-table to get this information, JOIN to it using the SQL Wizard or if you are manually writing the query, you can also use a IN (SELECT code FROM table WHERE field = value)