Link to home
Start Free TrialLog in
Avatar of rustypoot
rustypootFlag for United States of America

asked on

Crystal reports - SQL question

Hi, I have the Sql below in the crystal reports; it errors out on MRuN_int field. The error is 'Conversion failed when converting the varchar value 'I2683794' to data type int.

How do I resolve this error?



SET NOCOUNT ON
      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


Select Distinct CV3A.ClientDisplayName AS "Patient Name",
CV3A.CurrentLocation as Location,
CV3A.IDCode AS MRUN,
CONVERT(int,REPLACE(CV3A.IDCode, '-', '')) as MRUN_Int,
CONVERT(int, REPLACE([CV3A].[VisitIDCode], '-', '')) as AcctNum_Int,
CV3A.VisitIDCode AS "Account Number",
--CV3FR.Name AS "Primary Insurance",
CV3A.ProviderDisplayName AS "Admitting MD Name",
CV3A.AdmitDtm AS "Admit Date/Time",
CV3O.value AS "LOC from Order",
CV3A.typecode AS "SCM Type",
CV3A.CareLevelCode AS "SCM Care Level",
CV3A.ServiceDescription AS "SCM Service",
O.GUID AS "OrderGuid",
O.OrderCatalogMasterItemGUID,
case  when O.orderstatuscode = 'AUA1' then 'Active'
      when O.orderstatuscode = 'COMP' then 'Complete' End as "OrderStatus" ,
o.createdwhen


from CV3ActiveVisitList CV3A with (nolock)

inner join CV3Order O
on CV3A.ClientGUID = O.ClientGUID
and CV3A.GUID = O.ClientVisitGUID
--and O.orderstatuscode in ('AUA1', 'COMP')


inner  join CV3OrderUserData CV3O
on CV3A.clientguid = CV3O.clientguid
AND CV3O.OrderGUID = O.GUID
 
inner join CV3FRPContract CV3F
on CV3A.clientguid = CV3F.clientguid
inner join CV3FRP CV3FR
on CV3F.frpguid = CV3FR.GUID
 
 --where CV3A.AdmitDtm > DateAdd(hh, -24, GETDATE())
 --AND CV3A.AdmitDtm < DateAdd(hh, 0, GETDATE())
WHERE  CV3FR.Name <>  'PRIVATE PAY SECONDARY'
AND CV3O.Value in ('Outpatient/Overnight', 'Inpatient', 'Outpatient', 'Observation', 'Observation/Overnight')
and O.Name in ('Level of Care', 'Level of Care (HOI)')
--and O.Name in ('Level of Care (HOI)')
and O.orderstatuscode in ('AUA1', 'COMP')
and CV3F.Status = 'Active'
order by CV3A.AdmitDtm desc
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do a SELECT DISTINCT CV3A.IDCode FROM CV3A to see the possible values in there.
Avatar of Mike McCracken
Mike McCracken

What value do you want back if the field is non-numeric?

Perhaps simply bring the field into Crystal and do the conversion there.

mlmcc
To add a bit of explanation, the error says that IDCode has the value 'I2683794'.  In case you hadn't realized, that's an uppercase letter "i" (I) at the beginning.  Not the number one (1).  So, of course that's not going to convert to an integer.

 If you want to convert that column to an int, you need to determine what non-numeric values it might have and how you want to handle them.  For example, you could just remove the "I" and convert what's left, as dsacker showed, but could there be other characters in front of the number, or at other places in the string.

 You could use a query to check the values in that column, as dsacker suggested, but, assuming that you're using MS SQL, I would add a WHERE to only see the non-numeric values:

Select Distinct CV3A.IDCode
from CV3ActiveVisitList CV3A with (nolock)
WHERE ISNUMERIC (IDCode) = 0


 OTOH, do you really need that field to be an int in the first place?

 Also, if IDCode has non-numeric values, I'm guessing that there's a good chance that VisitIDCode will too, in which case you'll run into the same issues with that column, so you should probably check it too.

 James
Avatar of rustypoot

ASKER

Thanks
You are quite welcome.
dsacker's answer works in the specific  example given.  

To me the I is an indication that the data is being misentered or some other issue.  Perhaps the field is really supposed to be a string and the non-numeric characters are expected.  Thus the questions and comments

When you next encounter an E or S or other non-numeric in the data, will you resolve it with and ever growing REPLACE.

mlmcc