SQL 2005 - Conversion failed when converting the varchar value '100-TEST011' to data type int.
First and foremost, the SQL is handled dynamically by the SQL server, therefore some items in my WHERE clause will look odd to you - please disregard this.
I am getting the below mentioned error and need some help. Thanks in advance.
Msg 245, Level 16, State 1, Line 189
Conversion failed when converting the varchar value '100-TEST011' to data type int.
I am getting the below mentioned error and need some help. Thanks in advance.
Msg 245, Level 16, State 1, Line 189
Conversion failed when converting the varchar value '100-TEST011' to data type int.
SET NOCOUNT ON
CREATE TABLE #Summary
(
DoctorID int,
DoctorName varchar(60),
FacilityID int,
FacilityName varchar(60),
CompanyID int,
CompanyName varchar(60),
FinancialClassMID int,
FinancialClass varchar(60),
DepartmentMID int,
Department varchar(60),
InsuranceCarriersID int,
InsuranceCarrier varchar(60),
InsAllocation numeric(10,2),
PatAllocation numeric(10,2),
PatBalance numeric(10,2),
InsBalance numeric(10,2),
InsPayment numeric(10,2),
PatPayment numeric(10,2),
InsAdjustment numeric(10,2),
PatAdjustment numeric(10,2),
Flag varchar(60),
CPTCode varchar(10),
ProceduresID int,
TicketNumber varchar(35),
Entry datetime,
Resource varchar(60),
ResourceID int,
PatientVisitID int,
DateofServiceFrom datetime,
BatchId int
)
-- Create a copy
SELECT * INTO #Summary1 FROM #Summary
SELECT #Summary.*, 0 AS PatientId INTO #Summary2 FROM #Summary
DECLARE @db varchar(255)
SELECT @db=db_name()
IF len('11312,10735,10732,12026,11299,11309,11308,12025,11311,11310,10733,11294,10731')<2500
BEGIN
-- Insert Charges
IF 0 <> 1
BEGIN
INSERT INTO #Summary1
EXEC master..chcxp_CHC @db,3,NULL,NULL,'2','Entry','None',NULL,NULL, '11312,10735,10732,12026,11299,11309,11308,12025,11311,11310,10733,11294,10731'
INSERT INTO #Summary
SELECT * FROM #summary1
WHERE
--Filter on doctor
(
(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on CPTCode
(
(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on batches
(
(NULL IS NOT NULL AND BatchId IN (NULL)) OR
(NULL IS NULL)
)
TRUNCATE TABLE #Summary1
END
-- Next Import Payments and Adjustments
INSERT INTO #Summary1
exec master..chcxp_CHC @db,2,NULL,NULL,'2','Entry','None',NULL,NULL,0
END -- FQHCPROCS.ITEMDATA.U check
INSERT INTO #Summary
SELECT * FROM #summary1
WHERE --Filter on doctor
(
(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on CPTCode
(
(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on batches
(
(NULL IS NOT NULL AND BatchId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(0 = 0) OR
(0 = 1 AND DateofServiceFrom IS NULL)
)
IF 2 = 1
BEGIN
-- Now group the items together for a total
INSERT INTO #Summary2
SELECT
s.DoctorId,
DoctorName,
s.FacilityId,
FacilityName,
s.CompanyId,
CompanyName,
s.FinancialClassMID,
FinancialClass,
DepartmentMID,
Department,
InsuranceCarriersID,
InsuranceCarrier,
SUM(InsAllocation) AS InsAllocation,
SUM(PatAllocation) AS PatAllocation,
SUM(PatBalance) AS PatBalance,
SUM(InsBalance) AS InsBalance,
SUM(InsPayment) AS InsPayment,
SUM(PatPayment) AS PatPayment,
SUM(InsAdjustment) AS InsAdjustment,
SUM(PatAdjustment) AS PatAdjustment,
Flag,
CPTCode,
ProceduresID,
s.TicketNumber,
Entry,
ISNULL(d.ListName,'None') AS Resource,
ISNULL(pvr.ResourceID,0) AS ResourceID,
s.PatientVisitID,
DateofServiceFrom,
BatchID,
CONVERT(VARCHAR(20),pp.PatientId)AS PatientId
FROM #Summary s
JOIN PatientVisitResource pvr ON s.PatientVisitID = pvr.PatientVisitID
JOIN DoctorFacility d ON pvr.ResourceID = d.DoctorFacilityID
JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE
(
(NULL IS NOT NULL AND pvr.ResourceID IN (NULL)) OR
(NULL IS NULL)
)
END
ELSE
BEGIN
INSERT INTO #Summary2
SELECT s.DoctorId,
DoctorName,
s.FacilityId,
FacilityName,
s.CompanyId,
CompanyName,
s.FinancialClassMID,
FinancialClass,
DepartmentMID,
Department,
InsuranceCarriersID,
InsuranceCarrier,
InsAllocation,
PatAllocation,
PatBalance,
InsBalance,
InsPayment,
PatPayment,
InsAdjustment,
PatAdjustment,
Flag,
CPTCode,
ProceduresID,
s.TicketNumber,
Entry,
Resource,
ResourceID,
s.PatientVisitID,
DateofServiceFrom,
BatchID,
CONVERT(VARCHAR(20),pp.PatientId)AS PatientId
FROM #Summary s
JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
END
SELECT DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
FinancialClassMID,
FinancialClass,
DepartmentMID,
Department,
InsuranceCarriersID,
InsuranceCarrier,
SUM(InsAllocation) AS InsAllocation,
SUM(PatAllocation) AS PatAllocation,
SUM(PatBalance) AS PatBalance,
SUM(InsBalance) AS InsBalance,
SUM(InsPayment) AS InsPayment,
SUM(PatPayment) AS PatPayment,
SUM(InsAdjustment) AS InsAdjustment,
SUM(PatAdjustment) AS PatAdjustment,
Flag,
CPTCode,
ProceduresID,
TicketNumber,
Entry,
Resource,
ResourceID,
DateofServiceFrom,
CONVERT(VARCHAR(20),pp.PatientId)AS PatientId
FROM #Summary2
GROUP BY
DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
FinancialClassMID,
FinancialClass,
DepartmentMID,
Department,
InsuranceCarriersID,
InsuranceCarrier,
Flag,
CPTCode,
ProceduresID,
TicketNumber,
Entry,
Resource,
ResourceID,
DateofServiceFrom,
PatientId
ORDER BY
CompanyName,
FacilityName,
CPTCode,
TicketNumber
DROP TABLE #Summary
DROP TABLE #Summary1
DROP TABLE #Summary2
you seems to be iserting non integer value into any of these column.. check you data
DoctorId, ---
FacilityId, --
FacilityName,
CompanyId, ---
FinancialClassMID,--
DepartmentMID,---
InsuranceCarriersID,---
ProceduresID,---
ResourceID,-----
BatchID
DoctorId, ---
FacilityId, --
FacilityName,
CompanyId, ---
FinancialClassMID,--
DepartmentMID,---
InsuranceCarriersID,---
ProceduresID,---
ResourceID,-----
BatchID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT #Summary.*, 0 AS PatientId INTO #Summary2 FROM #Summary
You have very little control over data types that make up #Summary2. And if the exception line # matches with what I see, line 189 is:
INSERT INTO #Summary2 <--LINE 189
SELECT s.DoctorId,
DoctorName,
s.FacilityId,
FacilityName,
s.CompanyId,
CompanyName,
s.FinancialClassMID,
FinancialClass,
DepartmentMID,.....
This insert is trying to insert a varchar from #Summary into what SQL determined should be an INT field in #Summary2. I would replace any SELECT INTO statements, and instead, create any temp table structures you might need using a CREATE TABLE statement, where you can control the data type for each field. Hope this helps you!