|Note: The Database Viewer accesses the same database as OP. If you run a query that filters a lot of data, your database could become sluggish and affect your users. It is recommended to run queries during hours when users are not running OP.|
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')
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:
- 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.
- Once you have determined the SQL code that you want to use, select and copy the entire code. Be certain you are only copying the code (and not copying anything outside of the SQL text).
- In OP 14, select Tools > Database Viewer.
- Click the SQL tab.
- 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.
|Warning: Before saving any ready-made SQL in your OP Database Viewer, test the code to ensure your desired results appear. Based upon your configuration, parameters may not yield results (and may result in an error or null value when run or extracted).|
Warning: The OP Database Viewer can only display a limited number of results directly in the window. Therefore, some SQL queries must be extracted instead of run based on the size of the results. For large tables, please extract the file as a .csv to a spreadsheet program like Excel.
- 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 OK in the pop-up that appears.
- Once the code is entered into the SQL code field, click the Run or Extract button. Clicking the Run button will execute your query and display the results in the SQL grid at the bottom of the window. The Extract button will export the data into a comma-separated values file (.csv format file).
- 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, you will want to specify a certain date or date range. Otherwise, OP 14 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 14 for a while).
- 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.
- 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 appears in any OP 14 dashboard view).
- 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 Saving Files Exported from the Cloud in our OP Database Viewer Basics article.
- Locate where you saved the SQL file in your system. Double-click on the .csv file to launch the file in Excel.
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.
So, 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’s 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 wasn’t altered where you copied it. Also, verify from the source that the code hadn't changed 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'd remove the ">" symbol and space that follows for each line. Then, run the code again.