Using SQL to Write Reports

This section may be useful for writing Queries. 

Note:  When writing queries for reports, you should only use SELECT commands which return a table. Never try to change the actual data in the database from here using INSERT, UPDATE, DELETE, DROP, TRUNCATE, or any other similar command.

As a starting point, here are the basic elements of an SQL command, and how they are used:

For a list of tables and columns in the Open Dental database, see Programming Resources, Database Documentation.

Examples
SELECT ProcCode, AbbrDesc,Descript FROM ProcedureCode WHERE ProcTime = '/X/' AND ProcCode<= "D9999" ORDER BY ProcCode DESC LIMIT 10;
The following table was returned:

Here is a breakdown of the query:

SELECT ProcCode, AbbrDesc,Descript - Capitalization of column names are required in some mysql implementations.  It is a setting on the MySQL server.  For Linux the default is to be case-sensitive, for windows the default is case-insensitive.  It is best practice to match capitalization of the database column. You can leave a space after the commas or you can omit it. In the example, a combination of styles was used. The names of the columns must be spelled precisely as in the database. If you need to know the names of the columns, use one of the simple included queries for viewing the tables (in this case: SELECT * from procedurecode). The names of all the columns will be displayed with the table. For information on exactly what a column is used for, see Database Documentation. Also switch back and forth between human-readable and raw formats to get a feel for the data. In the raw format, much of the information is in numeric format and linked to a definition, a provider, or a patient.

WHERE ProcTime = '/X/' AND ProcCode<= "D9999" - The WHERE clause has two expressions which must both be true for a row to be included in the results. Single quotes were used in the first part and double quotes in the second part simply to illustrate that either is acceptable. The first expression is ProcTime = '/X/' . So the results only include procedures with that time pattern. If you wanted to show all rows that did not have that time pattern, you could either use ProcTime != '/X/' or ProcTime <>'/X/' . The first means 'not' and the second means 'less than or greater than'; the result is the same. Because the second part of the WHERE statement limited results to codes with values 'less than or equal to' D9999 , codes like N1234 would not show up. (N-codes are used in the example database for NoFee codes and are not ADA recognized). In this example, the raw format is the same as the human-readable format, but often you will need to use the raw value in your WHERE statement. For instance if you wanted to select appointments with unconfirmed status, it would look like this: SELECT * FROM appointment WHERE confirmed='19' . It is easy to see that 19 is the correct value to use by looking at the entire appointments table, finding an appointment with unconfirmed status and switching to the raw format which shows the numeric representations for columns like this. Alternatively, you could go to the definitions table, where the 19 originated and order the table by category. In the ApptConfirmed category, it clearly shows that the unconfirmed status uses a 19 to represent it in all other parts of the program.

ORDER BY ProcCode DESC - Orders the rows in descending order.

LIMIT 10 - Shows the first 10 rows of the results. Since this comes after the sorting, there will not be any skipped rows in the result unless they were excluded in the WHERE clause.

The order of all the commands listed above must always be in the order listed if used. ORDER BY can never come before WHERE, for instance.

More about raw formats: if you use database values in the where clause, not only do you have to use numbers as shown above, you also have to make sure your dates are in the correct format. For instance, the standard way of representing 3/13/03 in SQL is 2003-03-13. That's YYYY-MM-DD , which is really very logical but just unfamiliar to most users.

Some more useful commands:

AS - Lets you use an alias, or alternative name, for a column or table. The name of the column or table comes before the AS, and then the name you want to use follows it.

LIKE - Used in a WHERE clause if you are just doing a search on a portion of a value and not the entire value. For example, .....WHERE descript LIKE '%amalg%' only shows rows that have 'amalg' within the descript column. The % used on either side is the wildcard character. That means anything on either side of amalg is OK, the amalg could be anywhere within the description and it ignores capitalization.

LEFT JOIN - Used to pull information from two or more tables which are logically linked somehow. See the example below.

ON - The condition to use when doing a join. See the example:

SELECT T1.ProcCode,
f1.Amount AS '$ Standard',
f2.Amount AS '$ ODS',
f3.Amount AS '$ BCBS',
AbbrDesc,Descript
FROM procedurecode AS T1
LEFT JOIN fee AS f1 ON f1.FeeSched='53' AND T1.ProcCode=f1.ProcCode
LEFT JOIN fee AS f2 ON f2.FeeSched='55' AND T1.ProcCode=f2.ProcCode
LEFT JOIN fee AS f3 ON f3.FeeSched='54' AND T1.ProcCode=f3.ProcCode
ORDER BY ProcCode

The first thing to notice is the following four AS commands:
procedurecode AS T1
fee AS f1
fee AS f2
fee AS f3

So, the procedurecode table is referred to elsewhere in the query as T1, and the fee table is referred to as either f1, f2, or f3. Now the first line should make more sense. It means select the ProcCode column of the procedurecode table. As you can see, the name of the table is followed by a period and then the name of the column. This could just as easily be written procedurecode.ProcCode , but only if the AS had not been used. The second line means that the second column of the table will be the Amount column of f1(the fee table) and that the name of the second column will be '$ Standard' . The next two rows define the third and fourth columns, and the fifth row defines the fifth and sixth columns. The T1 is not used before each of the last two columns becausee those columns are only present in the procedurecode table, so it is not ambiguous. The T1 must be used in the first column because it would be unclear whether to use the ProcCode column from the procedurecode table or the fee table (even though they are exactly the same).

And then there is a feature of Open Dental to be aware of when forming queries that involve prices. It has to do with the way the automatic formatting is done to make a table human-readable. The only way Open Dental knows how to format a column is by checking the name of the column. If the name of the column exactly matches the expected name (changing caps is ok) then the column values are changed, right formatted for prices, etc. But in the example above, we are renaming 3 of the columns because otherwise they would all be 'Amount' and the report would be unclear. Since the columns are renamed, Open Dental won't recognize them. To solve this problem, Open Dental will also recognize any column that begins with a '$' as a price and will then format it correctly and show a sum total at the bottom of the column. That is the reason for naming each column above beginning with a $.

The last part of the example above that still needs to be addressed is the LEFT JOIN lines. The ON command specifies the conditions that must be satisfied for each join to take place. The first table that is joined is one where the ProcCode matches and the FeeSched is '53'. That number was obtained from the raw view of the definition table. This process is repeated for each of the other two fee schedules.