We help IT Professionals succeed at work.
Get Started

Crystal reports - SQL question

rustypoot
rustypoot asked
on
497 Views
Last Modified: 2012-07-10
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
Comment
Watch Question
Contract ERP Admin/Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE