SQL - Writing Queries

Layout is important When choosing your fields, the columns should be in some order that makes sense to the end user. If you are sorting by A,B and C, then A,B and C should be the first columns in your report/query, in that order. This way, when someone is reading the report, it is apparent immediately the order that the report is printed in.

Also, Notes fields should be the last field printed on a report. Notes fields can print up to 5000 characters which could be cumbersome to read in the middle of other data.

Alpha and Numeric fields If you have control in a Report Writer, your numeric fields should be RIGHT-JUSTIFIED and your alpha fields should be LEFT-JUSTIFIED. This way, numeric columns are totaled and their decimals are aligned.

Total your columns If you are printing data with numeric fields, sum them and present a total to the user. They will be happy you did.

Verify your data Just because you got data, it doesn't mean it is right. Verify your results and continue to do so for a week because there is nothing worse than a report in production with errors on it, and people are making decisions or paying commissions based on the results in the query you wrote.

Ignore redundant data If your query says STATE = 'FL', don't put the column State in your results. It's redundant. For example, if your report is only for "Station 1", then don't show the column "Station", since that is really the only data being printed in that report.

Give each column a title Name your columns so they make sense to the end user and don't make them too long. Short and sweet! If you are going to use that column in another place in your query (such as a WHERE clause or ORDER BY clause), then try not to put spaces in your column titles - you'll be glad that you didn't.

SUBTOTAL should use names If you are not using the SQL Wizard and writing free-form queries, name your SUBTOTAL fields the name of the column, don't use the index number of the column. This way, if someone changes the query later on, your SUBTOTAL's will continue to work as you originally designed.

Example:

SUBTOTAL 5 on 2

This will always work so long as the 5th column is what you want to total and 2 is the one you want to break on.

It's best to say:

SUBTOTAL Amount on 'Entry Date'

This way, if Amount becomes now the 7th column at a later point, it will still work properly without you having to go back and correct it. You'll notice that if the title has a space in it, you need to put the column title in single-quotes. That is why I recommend naming the column "EntryDate" instead of "Entry Date", it is easier.