Notifications
Clear all
Topic starter
I finally have a test server up and would like to learn some useful things on there without tearing up my production system.
Does anyone have any SQL queries they would like to share? I used to keep some SQL queries whenever GE fixed something for me but I think I deleted lots of them when upgrading to CPS 10 about a year ago. Any help would be appreciated, no documentation required. I would like to use this test server to run queries and reports from. Right now we pay people to do it from time to time. It would be nice to post them in a section here, like here is a SQL query to look for patients with <MRG> in their name for example...
I took a SQL class many years ago but they really didn't go over too much which would help GE specific SQL queries. It was mainly admin stuff like backup/restore and some SQL statements. I am looking at the GE website at the Data Models but it would help to see some statements so I can learn more about writing queries to do more administrative things.
Mike Zavolas
Tallahassee Neurological Clinic
Posted : September 15, 2012 2:35 am
I have a ton of various sql queries but they are for an Oracle database (EMR 9.5). Would you be interested in those?
David Shower
OU Tulsa School of Community Medicine
Posted : September 16, 2012 11:40 pm
Topic starter
Sure, that could give me some ideas. I actually have an old Oracle EMR server I could play with too.
Mike Zavolas
Tallahassee Neurological Clinic
Posted : September 16, 2012 11:48 pm
Posted : September 17, 2012 12:21 am
Mike,
I have a lot of different queries that I have written for sql. I will post them for you.
If you have any questions, let me know.
Ernie T.
Posted : September 17, 2012 1:20 am
Ernie, are these from an Oracle environment or a SQL Server environment?
Posted : September 17, 2012 1:26 am
Sql. Should work for version of cps 2006, to cps 10. May need minor adjustments but should work without any issues.
Word of caution, I do have several in there that will actually make changes to database tables. But most are labeled if that is an option.
Posted : September 17, 2012 1:28 am
Topic starter
ErnieT said:
Sql. Should work for version of cps 2006, to cps 10. May need minor adjustments but should work without any issues.
Word of caution, I do have several in there that will actually make changes to database tables. But most are labeled if that is an option.
Cool, thanks for the queries, guys. I have a VM of my test server so I can snapshot it before trying anything that will tear up the db.
Posted : September 17, 2012 11:50 pm
There are literally thousands of queries available to you if you look through the existing Stored Procedures and Views in the GE Database. Also, you can find many more by viewing the sql in the report module.
Also here is a link to GE's data dictionary:
Posted : January 18, 2013 5:23 am
I am interested in looking at those queries also and do not have access to that webpage. Would you happen to know who to contact to get access?
Posted : July 16, 2013 1:52 am
use this for both the username and password:
cpsuser
Posted : July 16, 2013 1:58 am
Thank you very much.
Posted : July 16, 2013 2:01 am
Is there a specific training that was good as far as GE specific database reporting goes ?
Not a GE course that just shows how to use canned reports or their ad-hoc query engine (very limited). Something that shows the meat of querying against the most commonly needed GE database tables and views (PERSON, USRINFO, RPTOBS, various appointment tables, and then there are new ones added for CPS 12...etc).
I make my way by going through the data dictionary, but it sure would be nice to have an actual (useful) class.
Is the data dictionary (and here) the only resource we have ?
I am looking for ideas of what has worked for others, in learning to be GE database knowlegable 🙂
- Beverly Blackman
Posted : November 6, 2014 8:53 am
