June 17, 2010 at 1:49 pm
Hi, I have a report that's pulling data off a sql 2005. after our server upgrade, this report stop working and im getting this error: "error converting data type nvarchar to numeric". Please help!!. Thank you all in advance.
SELECT PM.PAT_ID,
PD.LAST_NAME,
PD.FIRST_NAME,
EPI.EXT_PAT_ID AS SSN,
( SELECT TOP 1 SAMPLE_DRAW_DATE FROM ACCESSIONS WHERE PAT_ID = PM.PAT_ID AND SAMPLE_TYPE = 2 ORDER BY SAMPLE_DRAW_DATE DESC ) AS CURRENT_SERUM_DATE,
BT.BLOOD_TYPE_DESC,
CONVERT(Decimal(5,0), CP.MISC_LAB_VALUE) AS PRA_CURRENT,
CONVERT(Decimal(5,0), PP.MISC_LAB_VALUE) AS PRA_PEAK
FROMPATIENT_MEDICAL PM
INNER JOIN BLOOD_TYPES BT ON PM.BLOOD_TYPE_ID = BT.BLOOD_TYPE_ID
INNER JOIN HLA_PATIENT_STATUS HPS ON PM.PAT_ID = HPS.PAT_ID
INNER JOIN PATIENT_DEMO PD ON PM.PAT_ID = PD.PAT_ID
LEFT OUTER JOIN EXT_PAT_IDS EPI ON PM.PAT_ID = EPI.PAT_ID AND EPI.EXT_PAT_ID_TYPE = 1
LEFT OUTER JOIN ( SELECT PAT_ID, MAX(MISC_LAB_VALUE) AS MISC_LAB_VALUE FROM
(
SELECT ML.PAT_ID, CONVERT(Decimal(5,1), MISC_LAB_VALUE) AS MISC_LAB_VALUE
FROMPATIENT_MEDICAL PM
INNER JOIN BLOOD_TYPES BT ON PM.BLOOD_TYPE_ID = BT.BLOOD_TYPE_ID
INNER JOIN HLA_PATIENT_STATUS HPS ON PM.PAT_ID = HPS.PAT_ID
INNER JOIN MISC_LABS ML ON PM.PAT_ID = ML.PAT_ID
INNER JOIN ACTIONS A ON ML.PAT_ACT_ID = A.PAT_ACT_ID AND ML.PAT_ID = A.PAT_ID
INNER JOIN HLA_RESULT_CODES HRC ON ML.MISC_LAB_CODE_ID = HRC.MISC_LAB_CODE_ID
INNER JOIN ACCESSIONS ACC ON A.PAT_ID = ACC.PAT_ID AND A.ACCESSION_ID = ACC.ACCESSION_ID
INNER JOIN ACT_LIST AL ON A.ACT_ID = AL.ACT_ID AND HRC.ACT_ID = AL.ACT_ID
WHEREHRC.IS_ANTIBODY_SCREENING = 1
AND HPS.HLA_ACTIVE_ID = 1
AND HPS.ORGAN_TYPE = 4
AND HRC.SUMMARY_TITLE = 'PRA'
AND ISNUMERIC(ML.MISC_LAB_VALUE) = 1
) CONVERTER
WHERE MISC_LAB_VALUE > 0
GROUP BY PAT_ID ) PP ON PM.PAT_ID = PP.PAT_ID
LEFT OUTER JOIN ( SELECT PAT_ID, MAX(MISC_LAB_VALUE) AS MISC_LAB_VALUE FROM
(
SELECT ML.PAT_ID, ML.DATE_OF_LAB, CONVERT(Decimal(5,1), MISC_LAB_VALUE) AS MISC_LAB_VALUE
FROMPATIENT_MEDICAL PM
INNER JOIN BLOOD_TYPES BT ON PM.BLOOD_TYPE_ID = BT.BLOOD_TYPE_ID
INNER JOIN HLA_PATIENT_STATUS HPS ON PM.PAT_ID = HPS.PAT_ID
INNER JOIN MISC_LABS ML ON PM.PAT_ID = ML.PAT_ID
INNER JOIN ACTIONS A ON ML.PAT_ACT_ID = A.PAT_ACT_ID AND ML.PAT_ID = A.PAT_ID
INNER JOIN HLA_RESULT_CODES HRC ON ML.MISC_LAB_CODE_ID = HRC.MISC_LAB_CODE_ID
INNER JOIN ACCESSIONS ACC ON A.PAT_ID = ACC.PAT_ID AND A.ACCESSION_ID = ACC.ACCESSION_ID
INNER JOIN ACT_LIST AL ON A.ACT_ID = AL.ACT_ID AND HRC.ACT_ID = AL.ACT_ID
WHEREHRC.IS_ANTIBODY_SCREENING = 1
AND HPS.HLA_ACTIVE_ID = 1
AND HPS.ORGAN_TYPE = 4
AND HRC.SUMMARY_TITLE = 'PRA'
AND ISNUMERIC(ML.MISC_LAB_VALUE) = 1
AND CONVERT(varchar, ML.PAT_ID) + CONVERT(varchar, ML.DATE_OF_LAB) IN (
SELECT CONVERT(varchar, PAT_ID) + CONVERT(varchar, IDENTIFIER) AS IDENTIFIER FROM
(
SELECT PAT_ID, MAX(DATE_OF_LAB) AS IDENTIFIER FROM
(
SELECT ML.PAT_ID, ML.DATE_OF_LAB, CONVERT(Decimal(5,1), MISC_LAB_VALUE) AS MISC_LAB_VALUE
FROMPATIENT_MEDICAL PM
INNER JOIN BLOOD_TYPES BT ON PM.BLOOD_TYPE_ID = BT.BLOOD_TYPE_ID
INNER JOIN HLA_PATIENT_STATUS HPS ON PM.PAT_ID = HPS.PAT_ID
INNER JOIN MISC_LABS ML ON PM.PAT_ID = ML.PAT_ID
INNER JOIN ACTIONS A ON ML.PAT_ACT_ID = A.PAT_ACT_ID AND ML.PAT_ID = A.PAT_ID
INNER JOIN HLA_RESULT_CODES HRC ON ML.MISC_LAB_CODE_ID = HRC.MISC_LAB_CODE_ID
INNER JOIN ACCESSIONS ACC ON A.PAT_ID = ACC.PAT_ID AND A.ACCESSION_ID = ACC.ACCESSION_ID
INNER JOIN ACT_LIST AL ON A.ACT_ID = AL.ACT_ID AND HRC.ACT_ID = AL.ACT_ID
WHEREHRC.IS_ANTIBODY_SCREENING = 1
AND HPS.HLA_ACTIVE_ID = 1
AND HPS.ORGAN_TYPE = 4
AND HRC.SUMMARY_TITLE = 'PRA'
AND ISNUMERIC(ML.MISC_LAB_VALUE) = 1
) CONVERTER
WHERE MISC_LAB_VALUE > 0
GROUP BY PAT_ID
) A )
) A
GROUP BY PAT_ID ) CP ON PM.PAT_ID = CP.PAT_ID
WHEREHPS.HLA_ACTIVE_ID = 1
AND HPS.ORGAN_TYPE = 4
ORDER BY BT.BLOOD_TYPE_DESC,
PD.LAST_NAME,
PD.FIRST_NAME
June 17, 2010 at 2:13 pm
I don't think it has to do with the server upgrade...
it's more likely caused by the ISNUMERIC(ML.MISC_LAB_VALUE) = 1 function together with the CONVERT(DECIMAL...).
Reason: ISNUMERIC() will return 1 for values like '1E4', but you cannot convert this value to DEC5.
Example:
SELECT ISNUMERIC('1E4') -- will return 1
SELECT CONVERT(DECIMAL(5,0), '1E4') -- will fail with a conversion error
--If you'd like to test for numbers only you could use
SELECT CASE WHEN @t LIKE '%[^0-9]%' THEN 'false' ELSE 'true' END AS is_numeric
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply