A TRICK OF THE TRADE ("The SQL (sea-qual) -View")
By Nick Briseno
There are several methods for troubleshooting Siemens Novius® RIS UDR’s (User-Defined Reports), including the
showplan function built into Syngo-Workflow. Nevertheless, there are also other avenues providing simpler
methods for troubleshooting UDR’s. It’s an external process executed through MS-Access.
If you’ve ever written a query in MS Access by linking various tables whether historical or current patient
visit information, you’re probably half way there. Also, if you are a beginner at writing UDR’s, but familiar
with MS Access Queries, this would be a great way to learn the UDR format as well.
Take for instance, a report requiring a list of studies that were ended between given dates, and along the
line you may want to incorporate the procedure descriptions, ordering doctor names, patient medical record
numbers, department codes and procedure codes including the date the procedures were ended. Evidently when
speaking of procedures ended you immediately refer to historical data. While you may already know you need the
following tables from; activity, activity_info, item, doctor, patient, pat_name to provide all the necessary
information here is how the typical UDR query would look:
<<
DECLARE
@hosp varchar(6), @dr_last varchar(32), @dr_first varchar(32), @proc_dtime varchar(20), @dept char(3),
@proc_no char(4), @proc_desc_long varchar(32), @pt_last varchar(32), @pt_first varchar(32), @pt_med_rec_no
varchar(12)
SELECT patient_list cursor
activity.hosp, doctor.dr_last, convert(varchar(20),activity.proc_dtime,101), activity_info.dept,
pat_name.pt_last, pat_name.pt_first, patient.pt_med_rec_no, activity_info.proc_no, item.proc_desc_long
FROM
activity, activity_info, doctor, patient, pat_name, item
WHERE
(activity.proc_dtime between @p1_dtime and @p2_dtime) and
activity.acc_itn = activity_info.acc_itn and
activity_info.dr_no = doctor.dr_no and
activity.pat_itn = pat_name.pat_itn and
activity.pat_itn = patient.pat_itn and
activity_info.dept = item.dept and
activity_info.proc_no = item.proc_no and
activity.hosp = @p1
order by activity.hosp, activity_info.dept, patient.med_rec_no (this is the
sort criteria)
for read only
open patient_list
>>
<while patient_list into @hosp, @dr_last, @proc_dtime, @dept, @pt_last, @pt_first, @pt_med_rec_no, @proc_no,
@proc_desc_long>
This is what the typical MS Access SQL View would look like:
SELECT
dbo_activity.hosp, dbo_doctor.dr_last, dbo_activity.proc_dtime, dbo_activity_info.dept, dbo_pat_name.pt_last,
dbo_pat_name.pt_first, dbo_patient.pt_med_rec_no, dbo_activity_info.proc_no, dbo_item.proc_desc_long
FROM
dbo_item INNER JOIN ((((dbo_activity INNER JOIN dbo_activity_info ON dbo_activity.acc_itn =
dbo_activity_info.acc_itn) INNER JOIN dbo_doctor ON dbo_activity_info.dr_no =
dbo_doctor.dr_no) INNER JOIN dbo_pat_name ON dbo_activity.pat_itn =
dbo_pat_name.pat_itn) INNER JOIN dbo_patient ON (dbo_patient.pat_itn =
dbo_activity.pat_itn) AND (dbo_pat_name.pat_itn = dbo_patient.pat_itn)) ON
(dbo_item.proc_no = dbo_activity_info.proc_no) AND (dbo_item.dept =
dbo_activity_info.dept)
WHERE
(((dbo_activity.proc_dtime) Between #5/1/2007# And #5/15/2007#))
ORDER BY
dbo_activity.hosp, dbo_activity_info.dept, dbo_patient.pt_med_rec_no;
Seems slightly more complicated, but the actual visual of the query would look like this through MS
Access:
Nevertheless, the output generated by either would be identical. Commonly you would not use MS Access SQL
View code to incorporate into a typical UDR. However, if you were ever approached to produce a report you don’t
have, which would require a listing of items, and such, you can save time by writing your query in MS-Access and
directly copying the SQL View into a Syngo Workflow UDR. However, critical note to remember, you’ll need to
remove all the dbo_ from the SQL view you’ve copied from MS Access, since these will cause errors in RIS.
That is, take for instance the typical select statement in a RIS UDR would be:
SELECT patient_list cursor_
activity.hosp, doctor.dr_last, activity.proc_dtime, activity_info.dept, pat_name.pt_last, pat_name.pt_first,
patient.pt_med_rec_no, activity_info.proc_no, item.proc_desc_long
However, the typical select statement is an MS Access Query would look such as:
SELECT
dbo_activity.hosp, dbo_doctor.dr_last, dbo_activity.proc_dtime, dbo_activity_info.dept, dbo_pat_name.pt_last,
dbo_pat_name.pt_first, dbo_patient.pt_med_rec_no, dbo_activity_info.proc_no, dbo_item.proc_desc_long
MS Access adds the dbo_ as part of it’s method for connecting to Sybase Databases through ODBC (Open DataBase
Connectivity), which is not the actual name for the tables contained in the RIS Sybase Database.
Again, this method serves the purpose for producing alternative methods in troubleshooting as well as quick
report turnarounds that don’t require highly-involved customizations. Further, the queries are interchangeable,
such that, you can use the query as written in MS Access (excluding the dbo_) and incorporate into a RIS UDR.
Also, keep in mind that in MS Access you do not have to define the variables, which is something we did not note
or show an example for. If you would like more information on this subject or another RIS/PACS solution, please
just email us at vcs@getvitalized.com or call 610-444-1233.
Stay tuned for other Tricks of the Trade and helpful hints!