Notifications
Clear all
Topic starter
I have come up with a list of a few thousand documents missing OBS terms for Mammograms. Most of them are from employees importing via indexing client without entering the obs term, though some are from an interface issue. I was thinking of writing a SQL query to insert the obs terms in the notes. I have the SDID, PID, PVID, OBS date, OBS Value and a few other values that would be defaults for all entries. I am not sure how I would create an obsid and I think there may be a couple of other columns I might be required to fill in.
Has anyone done something like this? Is this a bad idea?
Posted : May 14, 2015 11:23 am
I have done this, I do not think it is a bad idea, I always do these types of things. I use this concept for my Immunization to OBS Sync script.
What you want to do is create a Loop and during that loop create a new OBSID for each one
something like this:::
--- BEFORE LOOP DECLARE YOUR DEFAULT OBSID
--Validation for OBSID
DECLARE @@OBSIDTBL TABLE (OBSID NUMERIC(19,0))
--Creates New ID for OBSID
DECLARE @OBSID NUMERIC(19,0)
SELECT @OBSID = CAST(DATEDIFF(second, CAST('01/01/1960' AS DATETIME), GETDATE()) AS NUMERIC) * 1000000
INSERT INTO @@OBSIDTBL
VALUES(@OBSID)
Next in your loop:::
--Set New OBSID
SET @OBSID = (SELECT TOP 1 OBSID FROM @@OBSIDTBL ORDER BY OBSID DESC) + 1
INSERT INTO @@OBSIDTBL
VALUES(@OBSID)i
Then Insert @OBSID to your INSERT script
i = to your loop variable, WHILE @i <= @COUNT concept. Also make sure you use BEGIN TRY and TRANSACTION, COMMIT OR ROLLBACK just in case something doesn't work right, so it doesn't apply your changes if an error occurs.
If you need assistance, please feel free to email me at [email protected]
Posted : May 14, 2015 11:34 am
I cast a vote for "a really bad idea". If you are a sophisticated database administrator then less bad idea. You have the potential for corrupting your data base.
Posted : May 14, 2015 2:39 pm
I agree with "bad idea." I was told by GE a long time ago that running scripts like that would void our contract. I also believe it's good practice to have any changes documented, so what I have done is to use Crystal Reports to generate an HL7 file and import the obs terms like a lab result. There may be a way to import the document as an append, but I haven't tried that. Below is the layout and attached is an .rpt that runs out of CPS based on criteria. I've also run similar reports from Excel files.
MSH|^~\&|eFormsLab|NFLWC|LinkLogic|CPS9|20150513123725||ORU|{1Record Number}|P|2.3|
PID|0001||{2PatientId}||{3Last}^{4First}^^||{5Birthdate}|{6Sex}|
PV1|0001|
OBR|0001|||^Patient Portal Linking|||20150513003725|||||||20150513003725||||Ext Oth||||20150513003725|||F
NTE|0001|eForms|Standardizing preferred method of contact to secmsg, patportalpin to Linked for previously linked patients::|
OBX|0001|ST|METHCONTACT^METHCONTACT||secmsg||||||F|||20150513003725|
OBX|0002|ST|PATPORTALPIN^PATPORTALPIN||Linked||||||F|||20150513003725|
Posted : May 15, 2015 3:04 am
Its only a bad idea, if you do not know what you are doing. I agree with that, but if you do, then it is fine, however you still want to make a backup prior to running any scripts on your live database. I would recommend doing it on a test database first. And if GE told you that, that is False! No where in the contract does it say you cannot run scripts. If you use any plugins with GE that another vendor has provided, such as Visalutions plugins, those run scripts all the time in your database.
Posted : May 15, 2015 3:08 am
Agreed, it is a bad idea mainly if you don't know what you're doing. Two recommendations:
1) Create a temporary table called OBS_Import. Create your new rows in there. After you have created them and verified they look perfect, then copy them into your OBS table. It's easy to create an OBS_Import table: generate the CREATE script for your existing OBS table, change the name, remove all the foreign keys, and if you're me, add a couple columns for an identity primary key (integer) and an IsImported flag (boolean).
2) Instead of creating an OBSID with the above script, call directly to the stored procedure dbo.getUniqueEMRID. Then you'll be guaranteed to not have any collisions with unique identifiers created by CPS. I also prefer to generate unique IDs at night when the office is closed, for extra safety.
*Edit: A bonus with the OBS_Import table is that a month/year from now, if the rows you created turn out to be causing a problem, you can go back and find them.
Posted : May 19, 2015 7:48 am
Topic starter
Justin,
I like the idea of using that function, but based on your syntax it looks like that is an MSSQL function/procedure and we are on Oracle (CEMR). I have looked through Oracle and I can not find the same thing listed there. I found something called GEN_EMR_ID that I think will work. Here is the code for that function:
create or replace FUNCTION GEN_EMR_ID RETURN NUMERIC AS
BEGIN
Return convert_date_to_id(sysdate)+id_seq.nextval;
END;
Currently we are generating the ID by using (ml.convert_date_to_id(SYSDATE) + rownum) which just adds 1 to the ID for each record it is inserting. I am going to swap the rownum out with +id_seq.nextval and id_seq.currval (for the pubtime and entryid fields) and give that a try. It should definatley be more unique than adding the rownumber.
This is the code for ID_SEQ:
CREATE SEQUENCE "ML"."ID_SEQ" MINVALUE 10 MAXVALUE 999990 INCREMENT BY 10 START WITH 11510 CACHE 50 NOORDER CYCLE ;
Posted : May 20, 2015 11:42 am
Yes, it looks like GEN_EMR_ID is the equivalent function in Oracle. You should just call that function directly to generate your unique IDs.
You can see the function is doing the same thing as the MSSQL equivalent stored procedure dbo.getUniqueEMRID. It generates an ID based on the number of seconds since a date (Jan 1, 1960 in the MSSQL case. Interesting side note - it's like the designers were trying to mimic Unix Time but used the wrong decade. Oops.) BUT - the system might want to generate more than one unique ID per second, and just using the seconds since a date would result in duplicate IDs. So they append the number with a value that increments for each unique ID generated. In MSSQL they they keep track of that number in the table dbo.emridcontrol. In Oracle they're using a sequence called ID_SEQ which rotates in a loop between 10 and 999990.
So when you need a unique ID, call GEN_EMR_ID once and get the result which is a numeric. Then write the result to your row. Repeat for each of your rows. That's THE way to guarantee your IDs are unique.
Posted : May 21, 2015 5:46 am
