0

I keep getting the error message

"Cannot read the next data row for the dataset... Conversion failed when converting the varchar value '4y 5m' to data type int"

when running an SSRS report that pretty much just returns the rows from my dataset. Below is the query populating the Dataset. Any ideas where it is getting the "4y 5m" value?

Note: if it matters, this is the select query that goes into a stored procedure.

Thanks!

SELECT distinct
      RAM.[AccountNumber] AS 'Account'
   ,RAM.[VisitID]
   ,RAM.[Facility_MisFacID]
   ,HRM.[Name]
   ,HRM.[Age]
   ,OOM3.[StartDateTime] AS 'OrderDate'
   ,PRA.[AdministrationDateTime] AS 'AdminDate'
   ,DATEDIFF(Minute,RAM.[ArrivalDateTime],OOM3.[StartDateTime]) AS 'DoorToOrder'
   ,DATEDIFF(Minute,RAM.[ArrivalDateTime],PRA.[AdministrationDateTime]) AS 'DoorToAdmin'
   ,DATEDIFF(Minute, OOM3.[StartDateTime],PRA.[AdministrationDateTime]) As 'OrderToAdmin'
   ,DPDD.TypeID
   ,REPLACE(REPLACE(REPLACE(OMAM.[MedicationName],'{',''),'}',''),'|',' ') AS 'Analgesic'
      ,PR.[RouteOfAdministration] AS 'Route'
   ,RAM.[ArrivalDateTime] AS 'ArrivalDate'
   ,RART.RegistrationTypeDischargeDateTime
   ,COALESCE(RART.[RegistrationTypeDischargeDateTime],
   (SELECT  MAX(EAA.[DateTimeID]) FROM  [livefdb].[dbo].[EdmActivity_Activity] EAA
   WHERE EAA.Status_EdmEdStatusID = 'DISCHARGED'
   AND EAA.SourceID = RAM.SourceID
   AND EAA.VisitID = RAM.VisitID
   AND EAA.ValueID = 'Dep Emergency')) AS 'DepartDate'
   ,mpn.NameStored as Physician
      ,(SELECT TOP 1 AAD.[DiagnosisCode_MisDxID] FROM [livefdb].[dbo].[AbsAcct_Diagnoses] AAD WHERE AAD.[SourceID] = RAM.[SourceID] AND AAD.[VisitID] = RAM.[VisitID] 
   and AAD.[SortOrder] = 1 AND AAD.[DiagnosisGrouperVersionField_MisGrouperVersionID] = ((SELECT TOP 1 AAD3.[DiagnosisGrouperVersionField_MisGrouperVersionID] 
   FROM  [livefdb].[dbo].[AbsAcct_Diagnoses] AAD3 WHERE AAD3.[SourceID] = AAD.[SourceID] AND AAD3.[VisitID] = AAD.[VisitID] ORDER BY AAD3.[DiagnosisGrouperVersionField_MisGrouperVersionID] DESC))
   AND replace(AAD.[DiagnosisCode_MisDxID], '.', '') IN (SELECT Code FROM [zcusSJC].[dbo].[dsg_LongBoneFracture_Codes$])) AS 'ICDCode' 
      ,(SELECT MDM.[Name] FROM [livefdb].[dbo].[MisDx_Main] MDM WHERE MDM.[SourceID] = RAM.[SourceID] AND MDM.[MisDxID] = (SELECT TOP 1 AAD.[DiagnosisCode_MisDxID] FROM [livefdb].[dbo].[AbsAcct_Diagnoses] AAD WHERE AAD.[SourceID] = RAM.[SourceID] AND AAD.[VisitID] = RAM.[VisitID] 
   and AAD.[SortOrder] = 1 AND AAD.[DiagnosisGrouperVersionField_MisGrouperVersionID] = ((SELECT TOP 1 AAD3.[DiagnosisGrouperVersionField_MisGrouperVersionID] 
   FROM  [livefdb].[dbo].[AbsAcct_Diagnoses] AAD3 WHERE AAD3.[SourceID] = AAD.[SourceID] AND AAD3.[VisitID] = AAD.[VisitID] ORDER BY AAD3.[DiagnosisGrouperVersionField_MisGrouperVersionID] DESC))
   AND replace(AAD.[DiagnosisCode_MisDxID], '.', '') IN (SELECT Code FROM [zcusSJC].[dbo].[dsg_LongBoneFracture_Codes$]))) AS 'ICDDescription' 
   ,(SELECT TOP 1 AAD.[DiagnosisCode_MisDxID] FROM [livefdb].[dbo].[AbsAcct_Diagnoses] AAD WHERE AAD.[SourceID] = RAM.[SourceID] AND AAD.[VisitID] = RAM.[VisitID] 
   AND replace(AAD.[DiagnosisCode_MisDxID], '.', '') IN (SELECT Code FROM [zcusSJC].[dbo].[dsg_LongBoneFracture_Codes$])) AS 'ICDCodeSecondary' 
      ,(SELECT MDM.[Name] FROM [livefdb].[dbo].[MisDx_Main] MDM WHERE MDM.[SourceID] = RAM.[SourceID] AND MDM.[MisDxID] = (SELECT TOP 1 AAD.[DiagnosisCode_MisDxID] FROM [livefdb].[dbo].[AbsAcct_Diagnoses] AAD WHERE AAD.[SourceID] = RAM.[SourceID] AND AAD.[VisitID] = RAM.[VisitID] 
   AND replace(AAD.[DiagnosisCode_MisDxID], '.', '') IN (SELECT Code FROM [zcusSJC].[dbo].[dsg_LongBoneFracture_Codes$]))) AS 'ICDDescriptionSecondary' 
   ,COALESCE((SELECT MPN.NameStored FROM [livefdb].[dbo].MisPerson_Names MPN WHERE RAM.SourceID = MPN.SourceID AND MPN.UnvUserID = EAM.[MidLevelProvider_UnvUserID])
   ,(SELECT MPPI.ProviderDisplayName FROM [livefdb].[dbo].MisPerson_ProviderInfo MPPI WHERE RAM.SourceID = MPPI.SourceID AND MPPI.UnvUserID = EAM.[MidLevelProvider_UnvUserID])
   ,EAM.[MidLevelProvider_UnvUserID]) AS 'Extender'
   ,case when (select top 1 DPDD.TypeID from [livendb].[dbo].[PhaRxMedications] PRM inner join [livendb].[dbo].[DPhaDrugData] DPDD ON PRM.[SourceID] = DPDD.[SourceID] AND PRM.[DrugID] = DPDD.[DrugID] where PRM.VisitID = RAM.VisitID and LEFT(DPDD.[TypeID],2) = '28' AND SUBSTRING(DPDD.[TypeID],4,2) = '08') is not null then 'Yes' else 'No' end as AnalgesicAdministered
   ,case when RAM.AdmitDateTime is null then 'No' when RAM.AdmitDateTime is not null then 'Yes' end as AdmittedCheck
   ,row_number() over (PARTITION BY RAM.VisitID ORDER BY OOM.OrderDateTime desc) as RowNumberCheck

 
 FROM [livefdb].[dbo].[RegAcct_Main] RAM
 INNER JOIN [livefdb].[dbo].[HimRec_Main] HRM ON RAM.[SourceID] = HRM.[SourceID] AND RAM.[PatientID] = HRM.[PatientID]
 INNER JOIN [livefdb].[dbo].[EdmAcct_Main] EAM ON RAM.[SourceID] = EAM.[SourceID] AND RAM.[VisitID] = EAM.[VisitID]
 INNER JOIN [livefdb].[dbo].[RegAcct_ProviderData] RAPD ON RAM.[SourceID] = RAPD.[SourceID] and RAM.[VisitID] = RAPD.[VisitID]
 INNER JOIN [livefdb].[dbo].[RegAcct_Registration] RAR ON RAM.[SourceID] = RAR.[SourceID] AND RAM.[VisitID] = RAR.[VisitID]
 INNER JOIN [livefdb].[dbo].[RegAcct_RegistrationTypes] RART ON RART.[SourceID] = RAM.[SourceID] AND RART.[VisitID] = RAM.[VisitID] AND RART.[RegistrationTypeKey_MisRegTypeID] = 'ER'
 INNER JOIN livefdb.dbo.MisPerson_Names mpn on mpn.SourceID = EAM.SourceID and mpn.UnvUserID = EAM.Provider_UnvUserID
 LEFT OUTER JOIN [livendb].[dbo].[PhaRxMedications] PRM on PRM.SourceID = RAM.SourceID and PRM.VisitID = RAM.VisitID
 LEFT OUTER JOIN [livendb].[dbo].[DPhaDrugData] DPDD ON PRM.[SourceID] = DPDD.[SourceID] AND PRM.[DrugID] = DPDD.[DrugID] 
 LEFT OUTER JOIN [livefdb].[dbo].[OmMedAcct_Medications] OMAM ON RAM.[SourceID] = OMAM.[SourceID] AND RAM.[VisitID] = OMAM.[VisitID] 
 LEFT OUTER JOIN [livefdb].[dbo].[OmOrd_ConnectionData] OOCD ON OOCD.[SourceID] = PRM.[SourceID] AND OOCD.[PhaPrescriptionUrn] = PRM.[PrescriptionID] and PRM.VisitID = RAM.VisitID and LEFT(DPDD.[TypeID],2) = '28' AND SUBSTRING(DPDD.[TypeID],4,2) = '08' --and OOCD.PhaPrescriptionStatus IN ('AC', 'DC', 'UNV')
 LEFT OUTER JOIN [livefdb].[dbo].[OmOrd_Main] OOM on RAM.[SourceID] = OOM.[SourceID] AND RAM.[VisitID] = OOM.[VisitID] AND OOM.[OmOrdID] = OOCD.OmOrdID AND OOM.[OmOrdID] = OMAM.[Medication_OmOrdID] 
 LEFT OUTER JOIN [livendb].[dbo].[PhaRx] PR ON OOCD.[SourceID] = PR.[SourceID] AND OOCD.[OmOrdID] = PR.[OrderId] AND OOCD.[PhaPrescriptionUrn] = PR.[PrescriptionID] 
 LEFT OUTER JOIN [livendb].[dbo].[PhaRxAdministrations] PRA ON OOCD.[SourceID] = PRA.[SourceID] AND OOCD.[PhaPrescriptionUrn] = PRA.[PrescriptionID] AND PRA.[AdministrationSeqID] = 1
 left outer join [livefdb].[dbo].[RegEvents_Events] ree on ree.SourceID = RAM.SourceID and ree.VisitID = RAM.VisitID and ree.Code LIKE '%ADM%' and ree.RegistrationStatus LIKE '%ADM%'
 left outer join [livefdb].[dbo].[RegAcct_RegistrationTypes] rert on rert.SourceID = RAM.SourceID and rert.VisitID = RAM.VisitID and rert.[RegistrationTypeDischargeDisposition_MisDischDisposID] = 'EXP'
 left outer join livefdb.dbo.OmOrd_Main3 OOM3 on OOM3.SourceID = OOM.SourceID and OOM3.OmOrdID = OOM.OmOrdID
  WHERE 
    CONVERT(DATE,RAM.[ArrivalDateTime]) BETWEEN @FromArrivalDate and @ThruArrivalDate
 AND LEFT(RAM.[AccountNumber],2) NOT IN ('J9', 'V9')
 AND LEFT(RAM.[AccountNumber],3) NOT IN ('J09', 'V09') AND (SELECT COUNT(1) FROM [livefdb].[dbo].[AbsAcct_Diagnoses] AAD WHERE AAD.[SourceID] = RAM.[SourceID] AND AAD.[VisitID] = RAM.[VisitID]
 AND replace(AAD.[DiagnosisCode_MisDxID], '.', '') IN (SELECT Code FROM [zcusSJC].[dbo].[dsg_LongBoneFracture_Codes$])) > 0
 AND DATEDIFF(MONTH,HRM.[Birthdate],RAM.[ArrivalDateTime]) > 23
 AND RAM.[Facility_MisFacID] IN ('SGA','CAN')
 and OOM.OrderDateTime is not null
 and (PR.[RouteOfAdministration] <> 'PO' or HRM.Age < 18)
 and HRM.Age >= 2
    and RAM.RegistrationStatus <> 'ADM'
 and ree.RegistrationStatus is null
 and rert.[RegistrationTypeDischargeDisposition_MisDischDisposID] is null 
 and OOCD.PhaPrescriptionStatus <> 'CAN'
Dom Vito
  • 507
  • 8
  • 38
  • Without Sample data, no. We can't run your query without access to your data, or DDL and sample data. We could hazard a few guesses, such as ask where you're comparing int's, but other than that. Are there any places your checking values for ints, but the value is stored as an (n)varchar? – Thom A Dec 04 '17 at 16:50
  • 2
    Looks like an age.. what's in your `age` field? These lines looks especially likely: `HRM.Age >= 2` `HRM.Age < 18` – Aaron Dietz Dec 04 '17 at 16:51
  • Looks like the age stuff was a problem.. thanks! – Dom Vito Dec 05 '17 at 19:05

0 Answers0