Back to Manual Table of Contents
The table viewer is intended only for SELECT commands which return a table. You should never try to change the actual data in the database from here using INSERT, UPDATE, or any other similar command. You might come back to this section after working with the table viewer for a while to understand how to navigate the different areas. As a starting point, here are the basic elements of an SQL command, and how they are used:
SELECT - The first word of the command. As with all the other commands, it does not need to be capitalized, but usually is for clarity.
* - Means 'all'. This usually follows the select command to specify that all columns are being selected. If you only wish to select certain columns, you would have to list them here instead of using the star.
FROM - Keyword that follows the columns. All other command come after FROM.
WHERE - Puts a limitation on the number of rows returned by specifying an expression after the WHERE. If the expression is true for that row, then it will be included in the rows returned. If the expression is false, it will not be present.
ORDER BY - One or more column names are listed after this command. The rows will be sorted in ascending order (A-Z) by the values in the given column. If a second column is listed, then items with identical values in the first column will be further ordered according to the values in that column. As many columns can be listed as needed.
DESC - Can follow the name of a column in the ORDER BY list to indicate it should be sorted in descending order (Z-A).
LIMIT - Followed by a number. The number specifies a limit on the number of rows to return. This would not normally be used.
Some examples with explanations:
SELECT ADACode, abbrdesc,Descript FROM procedurecode WHERE ProcTime = '/X/' AND ADAcode<= "D9999" ORDER BY adacode DESC LIMIT 10
The following table was returned:
There are many small details to point out. First of all, while writing this query, about half the time I submitted it, I got an error because I forgot something, usually a comma, or quote. Here is a breakdown of the query:
SELECT ADACode, abbrdesc,Descript . Notice that the capitalization of the columns does not matter, but whatever capitalization and order is used is what shows in the captions at the tops of the columns. 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, you can usually figure it out by looking at the name and the data within it, but later, more detailed documentation will be provided. 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 ADAcode<= "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 adacode 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.ADACode,
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.ADACode=f1.ADACode
LEFT JOIN fee AS f2 ON f2.FeeSched='55' AND T1.ADACode=f2.ADACode
LEFT JOIN fee AS f3 ON f3.FeeSched='54' AND T1.ADACode=f3.ADACode
ORDER BY ADACode
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 ADACode 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.ADACode , 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 ADACode column from the procedurecode table or the fee table (even though they are exactly the same).
This brings up some peculiarities of doing a query in DentOffice. There is a bug in one of the libraries that DentOffice depends on that causes an error if a table is returned which has two columns with the same name. It is not a big deal, but just a restriction to be aware of when forming queries. For this reason, you always have to specify the names of your columns when running a JOIN since a JOIN will, by default, return multiple columns with the same name. You will also have to specify which table the column comes from, as in the paragraph above.
And then there is a feature of DentOffice 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 DentOffice 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, DentOffice won't recognize them. To solve this problem, DentOffice 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 ADACode matches and the FeeSched is '53'. That number was obtained from the raw view of the fee table. This process is repeated for each of the other two fee schedules.