Scriptt error because returns more than 1 value

Scriptt error because returns more than 1 value

Postby gplourd » Thu Nov 14, 2013 8:51 am

In FilePatientRoutine, 159 Patient Advance Directives. I have a script to look to database and return value so that the existing entry does not get overwritten since the PM does not send this info. We are multi-org shared environment, and the script seems to work except for when the patient exists in multiple orgs, then I get the error, Script Error: <10>
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. <10>
Line: 24 <10>Column: 0Script Error: (30).
Any advice on how to resolved.
gplourd
 
Posts: 5
Joined: Thu May 23, 2013 6:44 am

Re: Scriptt error because returns more than 1 value

Postby jgaffga » Thu Nov 14, 2013 2:36 pm

Can you post a copy of the SQL so we can look it over? -jtg
jgaffga
 
Posts: 5
Joined: Thu Mar 01, 2012 4:41 pm
Location: Summit Medical Group, NJ

Re: Scriptt error because returns more than 1 value

Postby gplourd » Thu Nov 14, 2013 4:07 pm

vcommand = "Select entrycode from Directives_DE where id = (select directivesde from patient_directives where patientid = (select personid from patient_iorg where internalOrganization = 0 and OtherNumber = '" & MRN &"'))"
gplourd
 
Posts: 5
Joined: Thu May 23, 2013 6:44 am

Re: Scriptt error because returns more than 1 value

Postby jgaffga » Fri Nov 15, 2013 11:13 am

OK - SQL looks good.

Not much else you can do from the SQL side to fix this - you need to narrow-down your search better.
If you cannot do that, and you are willing to take just the 1st patient in the resultset (good or bad), then you could do something like:

vcommand = "Select entrycode from Directives_DE where id = (select directivesde from patient_directives where patientid = (select TOP 1 personid from patient_iorg where internalOrganization = 0 and OtherNumber = '" & MRN &"'))"
jgaffga
 
Posts: 5
Joined: Thu Mar 01, 2012 4:41 pm
Location: Summit Medical Group, NJ

Re: Scriptt error because returns more than 1 value

Postby tsuacci » Tue Nov 19, 2013 4:53 pm

As the other poster suggested, I would guess that the issue is in the last sub-query that searches for a patient strictly based on the OtherNumber field. There are no restrictions in place to prevent the OtherNumber from being duplicated for multiple patients; hence the SQL error. Therefore, you'll need some additional info when looking up the patient.

It appears that you have the patient's MRN available when making this script call. Do you also have the ID of the organization associated with the MRN when calling the script? If so, you could use those two pieces of information to uniquely identify the patient.

Alternatively, the patient's last name or DOB could be used as past of the query as well. These won't uniquely identify the patient 100% of the time, like the org/MRN combo, but it's better than what you are experiencing.

What type of interface of using this script?

Thanks,
Tyler
Tyler Suacci
Consultant
Galen Healthcare Solutions
707.335.4710
tyler.suacci@galenhealthcare.com
tsuacci
 
Posts: 55
Joined: Mon Apr 04, 2011 12:08 pm


Return to Tools: Queries, Scripts

Who is online

Users browsing this forum: No registered users and 1 guest