Overview

SQL (Structured Query Language) is the programming language OP uses to obtain demographic, clinical, billing, and administrative data.  It looks something like this:

select pca_id as transaction_number, patno as patient_chart, date1, 
pat_amount,deposit_method, deposit_source, staffname from patient_credit_acct
left outer join staff1 on staff1.staffid = patient_credit_acct.staffid where date1 = :date_to_check
and  pat_amount <> 0 and deposit_method in ('CC','CA','CK')

Run SQL

The SQL Code Clip-and-Save Library in the Help Center can provide you with various SQL samples. However, SQL codes can be obtained from other locations. While searching or creating SQL:

1. Identify the SQL snippet that you want to use. Wherever you found the SQL code, check for a description that will inform you about what the code looks for (productivity, financials, diagnostic tests, etc). Also, check for how the report is intended to be used.
2. Once you have determined the SQL code that you want to use, select and copy the entire code. Only copy the code (do not copy anything but the SQL text).
3. Click the Tools tab > Database Viewer.
4. Click the SQL tab.

1. In the SQL code field, paste the SQL code that you had copied previously. In the image below, we are pasting the code that appears near the beginning of this article.

1. Once the code is pasted in the SQL Code field, click the Save SQL Query Text Under a New Name  button. Enter the the Name for the query and click the OK button in the pop-up box that is displayed.
2. Once the code is entered into the SQL code field, click the Run or Extract button. The Run button will executes your query and display the results in the SQL grid at the bottom of the window. The Extract button exports the data into a comma-separated values file (.csv format file).  
3. You will be prompted to enter a parameter or set of parameters via a pop-up window. For example, if you are running a report that asks for money collected at the front desk, specify a certain date or date range. Otherwise, OP will attempt to find ALL the money your front desk has ever collected, which could take a very long time (especially if you have been using OP for a while).

1. Click the Run button to display the SQL results in the SQL tab grid (shown in the image below) or click the Extract button to display a large amount of results in a .csv file.  

1. When viewing the results in the Database Viewer, you can sort, rearrange, filter, and group the data displayed in the grid (just like data that is displayed in any OP dashboard view).  
2. The results can be exported from the SQL Database Viewer by entering a filename in the Results Filename field and then clicking the Save CSV button.  If you click the Save CSV button before entering a name in the Results Filename field, you will be prompted to provide a filename. If you are an OP Cloud Client, follow the instructions in the Saving Files Exported from the Cloud  section of the OP Database Viewer Basics article.
3. Locate where you saved the SQL file in your system.  Double-click on the .csv file to launch the file in Excel.

Send SQL Query/Results to Patients

You can send a SQL query/results to all visible patients. After running a query on the SQL tab, click the Send button. A confirmation box is displayed asking if you want to send messages to all visible patients. Click the OK button. The Patient Message eXchange window is displayed. Click here for more information on using the Patient Message eXchange.

Troubleshooting

This section addresses some of the common possible errors you may receive when applying SQL code in the database viewer. 

Blank Result Pane

Sometimes you might receive a blank result pane when you run a report.

• A blank result pane may not actually be the result of an error. Some reports should actually come back blank (with empty results). For example, you have may a query asking for data on all the work performed that hasn't yet been billed. If the results return blank in the grid, then that's great!  It could mean that all the work performed was already billed.
• A blank result may indicate that there is a difference in your database compared to the SQL code that you entered in the SQL code pane.  For example, if the SQL looks for established sick visits by searching for EST SICK and you call them EST. SICK (with a period), your report will display nothing. Alternatively, you may have entered a parameter incorrectly.

Pop-up Error Message

Sometimes you will receive a pop-up error message when running SQL in the Database Viewer.

An error message generally means that the syntax of your SQL code is incorrect. It could be incorrect because:

• The code is incorrect.
• The code is correct, but it became incorrect during the copy and paste process.

If you receive an error message, check the following:

• Verify the copied SQL code is exactly what you copied. Make sure you copy and paste the complete SQL (and ONLY the complete SQL) from the source to the SQL code field in the database viewer.  
• Make sure there is nothing at the top or bottom of the SQL code text field that remains from a previous query. You may need to scroll up and down in that field to make sure no stray characters exist.
• Verify the SQL was not altered where you copied it.  Also, verify from the source that the code did not change before you saw it. For example, many email programs add characters to show that a message was forwarded. Let’s say someone wrote the following SQL and emailed it to your colleague:

  Select patno, fname, lname, birthdat
  From register
  Where patno > 99 and status_pat = ‘ACTIVE’

Your colleague then forwards the email to you:

> Select patno, fname, lname, birthdt
> From register
> Where patno >99 and status_pat = ‘ACTIVE’

If you copy and paste the lines with the extra ">" symbol, an error will occur. To resolve this specific problem, you would remove the ">" symbol and space that follows for each line. Then, run the code again.