June 30, 2009 at 10:58 am
Our company's EMR has migrated from SQL Server 2000 to SQL Server 2005. I had created about 15 reports that all worked without any issues prior to the migration. In fact, I was "enhancing" the reports the day before the upgrade and again, everything worked fine.
Since the upgrade to SQL Server 2005, 5 of the 15 reports do not work. All 5 of the reports are generating an Arithmetic overflow error.
I've been trying to resolve the issue 1 report at a time but I can't get past the first one.
The query I use for this report is listed below.
--Select everything from Qresult1 and Qresult2 ON patid match between Q1 and Q2
SELECT DISTINCT * FROM
--Qresult1
--Finds max Hgb labdate for all patients returned in resultset1.
(SELECT id, lname, MAX(obs_date_time) AS LabDate FROM
--ResultSet1
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname,
dbo.lab_result.resulted_test_id, UPPER(dbo.lab_result.resulted_test_desc) AS LabType,
dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key
WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') ResultSet1
GROUP BY lname, id) QResult1 --END OF Qresult1 Data Ouput
FULL OUTER JOIN
--Qresult2
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
--Also returns all patient problems.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, dbo.lab_result.resulted_test_id,
UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time,
dbo.Patient_Problem.problem_code, dbo.Patient_Problem.problem_description, Replace(problem_description,'Anemia Management','AAA') as ProbDescAbbrev,
dbo.Patient_Problem.notes, dbo.Patient_Problem.problem_date
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key LEFT OUTER JOIN
dbo.Patient_Problem ON dbo.mpi.blind_key = dbo.Patient_Problem.blind_key
WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') QResult2 --END OF Qresult2 Data Ouput
ON QResult1.id = QResult2.id
WHERE QResult2.problem_code = 11
ORDER BY QResult2.hcl, QResult2.lname, QResult2.ProbDescAbbrev
[/code]
The query works fine if I comment out the next to last line of code in the query [code]
WHERE QResult2.problem_code = 11[/code]
The query also works fine if I uncomment out the next to last line of code AND then comment out this line in the first WHERE statement[code]
AND convert(numeric(18,2), result_value) <= '10.0'[/code]
I am stumped. The error code is Msg 8115, Level 16, State 6, Line 2
Arithmetic overflow error converting varchar to data type numeric.
I've tried altering the convert function in the first WHERE statement several times as listed below:
[code]
cast(ltrim(rtrim(substring(result_value,0,18))) as decimal(18,2)) <= '10'[/code]
Nothing seems to work. Any help would be greatly appreciated.
Amedeo
June 30, 2009 at 11:06 am
Amedeo Feroce (6/30/2009)
Our company's EMR has migrated from SQL Server 2000 to SQL Server 2005. I had created about 15 reports that all worked without any issues prior to the migration. In fact, I was "enhancing" the reports the day before the upgrade and again, everything worked fine.Since the upgrade to SQL Server 2005, 5 of the 15 reports do not work. All 5 of the reports are generating an Arithmetic overflow error.
I've been trying to resolve the issue 1 report at a time but I can't get past the first one.
The query I use for this report is listed below.
--Select everything from Qresult1 and Qresult2 ON patid match between Q1 and Q2
SELECT DISTINCT * FROM
--Qresult1
--Finds max Hgb labdate for all patients returned in resultset1.
(SELECT id, lname, MAX(obs_date_time) AS LabDate FROM
--ResultSet1
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname,
dbo.lab_result.resulted_test_id, UPPER(dbo.lab_result.resulted_test_desc) AS LabType,
dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key
WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') ResultSet1
GROUP BY lname, id) QResult1 --END OF Qresult1 Data Ouput
FULL OUTER JOIN
--Qresult2
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
--Also returns all patient problems.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, dbo.lab_result.resulted_test_id,
UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time,
dbo.Patient_Problem.problem_code, dbo.Patient_Problem.problem_description, Replace(problem_description,'Anemia Management','AAA') as ProbDescAbbrev,
dbo.Patient_Problem.notes, dbo.Patient_Problem.problem_date
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key LEFT OUTER JOIN
dbo.Patient_Problem ON dbo.mpi.blind_key = dbo.Patient_Problem.blind_key
WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') QResult2 --END OF Qresult2 Data Ouput
ON QResult1.id = QResult2.id
WHERE QResult2.problem_code = 11
ORDER BY QResult2.hcl, QResult2.lname, QResult2.ProbDescAbbrev
The query works fine if I comment out the next to last line of code in the query
WHERE QResult2.problem_code = 11
The query also works fine if I uncomment out the next to last line of code AND then comment out this line in the first WHERE statement
AND convert(numeric(18,2), result_value) <= '10.0'
I am stumped. The error code is Msg 8115, Level 16, State 6, Line 2
Arithmetic overflow error converting varchar to data type numeric.
I've tried altering the convert function in the first WHERE statement several times as listed below:
cast(ltrim(rtrim(substring(result_value,0,18))) as decimal(18,2)) <= '10'
Nothing seems to work. Any help would be greatly appreciated.
Amedeo
Have you tried changing this: WHERE QResult2.problem_code = 11
to this: WHERE QResult2.problem_code = '11'
June 30, 2009 at 11:28 am
Lynn Pettis
Have you tried changing this: WHERE QResult2.problem_code = 11
to this: WHERE QResult2.problem_code = '11'
I actually had it listed as you suggested and removed the quotes around the 11 right before I copied and pasted my query to this forum.
I get the same error whether I use
WHERE QResult2.problem_code = '11'
OR
WHERE QResult2.problem_code = 11
Thanks,
Amedeo
June 30, 2009 at 12:02 pm
What's the data type for problem_code?
Could you move the filtering of problem_code into the derived table? If you do this, do you still get the error?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2009 at 12:05 pm
what happens if you cast to FLOAT instead of Numeric(18,2)? Your cast will go BOOM if you have more than 16 before the decimal.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 30, 2009 at 2:19 pm
The result_value and problem_code are both varchar datatypes.
I have not tried creating a derived table to filter the problem_code. I will try that and see what happens.
I tried using FLOAT but that threw another error:
Error converting datatype varchar to real
I googled this error and found the follwing article which suggests that there is an issue with the data.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21404498.html
When I run this query
select result_value from lab_result
where charindex(char(13), result_value) > 0
I get the following results:
No growth
82
Ye llow
Cle ar
Negati ve
Negati ve
Negati ve
Nega tive
COM MENT
COMMENT
COM MENT
COMMENT
CO MMENT
COM MENT
COMMENT
Y ellow
Cl ear
Negat ive
Negat ive
Negat ive
Neg ative
Is it possible the isNumeric() function in my WHERE statement isn't really working and these values are causing the errors? I've read on other blogs that the isNumeric()function doesn't always work.
June 30, 2009 at 2:24 pm
I think this error is mainly being caused by the convert() function within the first WHERE statement. As I originally stated, the query was working prior to the migration to SQL Server 2005. I broke the query down into small pieces and had everything working until I included that last WHERE statement that filtered on problem_code which then created the Arithmetic Overload error I was prevously receiving.
That's what confuses me the most. The query runs fine when I comment either one of these statements. However, if I leave them both uncommented, the query errors out.
WHERE QResult2.problem_code = 11
AND convert(numeric(18,2), result_value) <= '10.0' (in the first WHERE statement)
Why did this query work without any issues prior to the migration SQL Server 2005?
June 30, 2009 at 2:46 pm
Differences in the query engine. Perhaps in SQL Server 2000 the WHERE creteria ISNUMERIC(...) = 1 may have filtered records out before the creteria with the CONVERT on it was processed so that it didn't encounter non-numeric data.
Of course this is just a guess on my part.
June 30, 2009 at 2:58 pm
I agree it does appear to be caused by the Convert in the final WHERE clause.
You appear to have lucked out so far with IsNumeric() actually working the way most people think it does on result_value because IsNumeric() does NOT mean that the value is convertible to a numeric data type (see this blog post) (".", "$", and other characters return true for IsNumeric() and cannot be converted to a numeric data type).
Have you tried to filter out non-numeric data from problem_code?
I would definitely recommend, again, that you take the filter on problem_code and place it in the derived table. Currently it looks you are returning more rows than necessary since you are eliminating all rows with a problem_code other than 11.
As Lynn has said the order the query engine is doing things in 2005 may have changed from 2000. Or, your data may have changed and now you have data in the table that no longer converts to numeric. These issues will happen when you use a non-numeric data type to store data on which you want to do numeric functions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2009 at 4:57 pm
You cannot guarantee in what order SQL will evaluate your conditions so the CONVERT might happen before or after the ISNUMERC:WHERE id_type = 'MR'
AND isnumeric(result_value) = 1 -- Issue
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0' --Issue
The only way I've found to get around this in 2005 or later is to use a derived table.
July 1, 2009 at 10:44 am
Sorry for the delayed reply. Report writing isn't my primary responsiblity so I'm limited on how much time I can spend on this.
I'm still leaning towards the fact that perhaps the data was corrupted during the migration. Even though the data was originally stored in a SQL Server 2000 database I was still running queries from SSMS 2005 (which was installed on my machine). It still doesn't make any sense to me why these queries don't work anymore. Of the 5 reports that I said don't work, each one fo them uses a convert() function. The ten reports that did work without issues did not use the convert() function.
Anyways, Jack got me thinking that I was returning too many rows in my second resultset so I filtered out the problem code in the WHERE statement of my second resultset and the query works. Since I'm doing a full outer join between resultset1 and resultset2 there are rows where all the fields for resultset2 are NULL but that is okay becuase I can filter those out of the report in SSRS 2005.
A derived table might be the better option but I'm going to go with this for now since my time is limited and I have to get 4 other reports running. Thanks to everyone who commented. It is really appreciated.
Can you mark a thread as the answer on this forum?
Here is the query that worked.
--Select everything from Qresult1 and Qresult2 ON where patid match between Q1 and Q2
SELECT DISTINCT * FROM
--Qresult1
--Finds max Hgb labdate for all patients returned in resultset1.
(SELECT id, lname, MAX(obs_date_time) AS LabDate FROM
--ResultSet1
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname,
dbo.lab_result.resulted_test_id, UPPER(dbo.lab_result.resulted_test_desc) AS LabType,
dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key
WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') ResultSet1
GROUP BY lname, id) QResult1 --END OF Qresult1 Data Ouput
FULL OUTER JOIN
--Qresult2
--Returns all labdates for patients who have a hemoglobin lab equal to or below 10.
--Also returns all patient problems.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, dbo.lab_result.resulted_test_id,
UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units, dbo.lab_result.obs_date_time,
dbo.Patient_Problem.problem_code, dbo.Patient_Problem.problem_description, Replace(problem_description,'Anemia Management','AAA') as ProbDescAbbrev,
dbo.Patient_Problem.notes, dbo.Patient_Problem.problem_date
FROM dbo.lab_result INNER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.blind_key INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key LEFT OUTER JOIN
dbo.Patient_Problem ON dbo.mpi.blind_key = dbo.Patient_Problem.blind_key
WHERE id_type = 'MR'
AND problem_code = '11'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')
AND convert(numeric(18,2), result_value) <= '10.0'
AND obs_date_time BETWEEN '04/01/2009' AND '06/25/2009') QResult2 --END OF Qresult2 Data Ouput
ON QResult1.id = QResult2.id
ORDER BY QResult2.id, QResult1.id
July 1, 2009 at 10:48 am
Nope, you can't mark a thread as answered. Reason being some one may look at your solution and provide you with another alternative (note, I didn't necessarily say better) solution. In addition, discussions sometimes take place over suggested solutions that offer opportunities for additional learning.
July 1, 2009 at 10:58 am
Thanks Lynn. I appreciate your time and responses just as do the others that posted.
Amedeo
July 13, 2009 at 9:22 am
As I mentioned in my initial post, my company migrated from a SQL Server 2000 database to a SQL Server 2005 database.
Since this migration, I've had to fix all the reports that were working prior to the upgrade.
I'm down to my last 3 reports and I'm still receiving the dreaded Arithmetic overflow error.
I'm copying and pasting a small section of a larger query.
--Q5 returns all Ferritin, Tsat, and Hemoglobin lab dates along with the values
--for those labs within the end-user specified date range and lab values.
--Also return appointment dates.
(SELECT dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, CONVERT(nvarchar(20), dbo.mpi.dob, 101) AS DOB, DATEDIFF(Year,
dbo.mpi.dob, GETDATE()) AS Age, UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units,
CONVERT(nvarchar(20), dbo.lab_result.obs_date_time, 101) AS LabDate,
CONVERT(nvarchar(20),appt_date,101) AS ApptDate
FROM dbo.mpi INNER JOIN
dbo.mpi_xref ON dbo.mpi.blind_key = dbo.mpi_xref.blind_key INNER JOIN
dbo.lab_result ON dbo.mpi.blind_key = dbo.lab_result.blind_key LEFT OUTER JOIN
dbo.appointment ON dbo.mpi.blind_key = dbo.appointment.blind_key
WHERE mpi_xref.id_type = 'MR'
--AND ((DATEDIFF(Year,mpi.dob,GETDATE()) >= '18') AND (DATEDIFF(Year,mpi.dob,GETDATE()) = '0'
-- AND cast(ltrim(rtrim(substring(result_value,0,18))) as decimal(18,2)) <= '10.0')
)
AND (convert(datetime,obs_date_time) BETWEEN '06/01/2009' AND '06/25/2009'))
I've labeled conditions within the WHERE statement as section 1, section 2 and section 3. If I comment out any two of these three sections, the query works. If I uncomment two or more of these sections than I receive the dreaded Arithmetic Overflow error.
All of the manipulation I'm doing to the result_value field (i.e. cast, ltrim, rtrim) have been added as a work around for the error I keep receiving.
Any help would be greatly appreciated.
July 13, 2009 at 9:38 am
I was doing a quick scan, so hopefully I did not miss it, but did you try a derived table yet?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply