SQL Tip -  Find Queries, Document Sections and Interventions (MEDITECH 6.x Only)

A report request arrives on your desk – complete with screenshots of screens from MEDITECH complete with circles and arrows. But the fields are unfamiliar… a query to the requestor for a Shift-F9 and screenshot of the resulting pop-up yields nothing. “Ah,” you think, “custom queries on a CDS, document section or intervention. Why oh why didn’t MEDITECH code Shift-F9 so it would tell us what query mnemonic was in play? Why?”

But fear not, thanks to the DR xfer process also covering dictionaries, you can use SQL to query the build of screens, assessments, and interventions. Attached to this tip is a stored procedure that searches the dictionaries for usage of queries and lets you know not only which screens they are used on, but which collector tables contain the responses!

As with all of our example SQL code, you will need to run the script against your DR to compile a named query (or stored procedure) that you can then call with a simple SQL command like:

Note that each parameter drives a LIKE predicate in the code, so you can search for partial strings (or use wildcards like % and _).

Also note that you can mix the fields, using some or all of them.

Those screen shots you got? With the circles and arrows? Use the text labels from the fields to search by Query Text and you will find what you’re looking for.

In this example our search finds us five records:

Here we see that the CAN.OX query can be find on four different document sections in three different interventions – or as a standalone.

Particularly when you are trying to retrieve PCS assessment query data, your speediest way to get at the response is to first identify potential visits, then search those visit’s interventions, then document sections and then finally the query/response data. Knowing which interventions and document sections hold the queries you are looking for will speed up that process enormously.

A Warning Note: But wait, you say, what about RegAcctQuery_Result? Doesn’t it hold all of the PCS query responses? Isn’t it designed to be reported against so you don’t have to plunge into the hellish complexity of the PCS tables?

Yes, but… a query needs to have its AccountQuery flag set to Y before the responses will flow over to RegAcctQuery_Result and the background job that populates the table from the PCS tables needs to be working flawlessly.

Unfortunately, for the data that you need for this latest report… that probably won’t be true. So back to the PCS mines for you!

Bonus! Attached to this tip, in addition to the SQL code, is a Microsoft Reporting Services report to package it up all pretty-like and make it easy to distribute to your analysts.

Extra Credit: When you examine the code, you’ll find that the @cRowCountYN parameter does not search every single possible response storage location. Add some more! Particularly if you know there are custom screens attached to modules like MM or BAR at your organization.