SQL 2005 Error - Conversion failed when converting the varchar value '100.81' to data type int.
PLEASE NOTE, the SQL is handled dynamically by the server - alot of this query wont make sense or look right to you --- mostly in the WHERE clauses. Please, please, please dont ask my why NULL=NULL and other related questions about my WHERE clause statements ... again, its handled dynamically.
My issue has to do with this error:
Msg 245, Level 16, State 1, Line 195
Conversion failed when converting the varchar value '100.81' to data type int.
I tried:
CONVERT(VARCHAR(20), ISNULL(vd.DiagnosisCode , ''))AS Diagnosis
and I still get this issue. What am I missing?
My issue has to do with this error:
Msg 245, Level 16, State 1, Line 195
Conversion failed when converting the varchar value '100.81' to data type int.
I tried:
CONVERT(VARCHAR(20), ISNULL(vd.DiagnosisCode , ''))AS Diagnosis
and I still get this issue. What am I missing?
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 Diagnosis INTO #Summary2 FROM #Summary
DECLARE @db varchar(255)
SELECT @db=db_name()
IF len('51')<2500
BEGIN
-- Insert Charges
IF 0 <> 1
BEGIN
INSERT INTO #Summary1
EXEC master..chcxp_CHC @db,3,NULL,NULL,'2','Entry','None',NULL,NULL
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,
ISNULL(vd.DiagnosisCode , '') AS Diagnosis
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
LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1
WHERE
(
(NULL IS NOT NULL AND pvr.ResourceID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on diagnosis
(
(NULL IS NOT NULL AND vd.DiagnosisId 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,
ISNULL(vd.DiagnosisCode , '') AS Diagnosis
FROM #Summary s
JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1
WHERE ---Filter on diagnosis
(
(NULL IS NOT NULL AND vd.DiagnosisId IN (NULL)) OR
(NULL IS NULL)
)
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,
Diagnosis
FROM #Summary2
GROUP BY
DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
FinancialClassMID,
FinancialClass,
DepartmentMID,
Department,
InsuranceCarriersID,
InsuranceCarrier,
Flag,
CPTCode,
ProceduresID,
TicketNumber,
Entry,
Resource,
ResourceID,
DateofServiceFrom,
Diagnosis
ORDER BY
CompanyName,
FacilityName,
CPTCode,
TicketNumber
DROP TABLE #Summary
DROP TABLE #Summary1
DROP TABLE #Summary2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OBonio,
Where exactly am I placing "select CAST(CAST('100.81' AS DECIMAL(10,2)) AS INT)" ??
Where exactly am I placing "select CAST(CAST('100.81' AS DECIMAL(10,2)) AS INT)" ??
Well, it looks like you're trying to place vd.Diagnosis code into an INT somewhere, so if you use CAST(CAST(vd.DiagnosisCode
ASKER
MNelson831
Select convert(int,'100.81') returns:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '100.81' to data type int.
Select convert(int,Left('100.81',
100
Select convert(int,100.81) returns:
100
Select convert(int,'100.81') returns:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '100.81' to data type int.
Select convert(int,Left('100.81',
100
Select convert(int,100.81) returns:
100
ASKER
OBonio -
With your recommendation, I get this error now:
Msg 515, Level 16, State 2, Line 263
Cannot insert the value NULL into column 'Diagnosis', table 'tempdb.dbo.#Summary2_____
The statement has been terminated.
With your recommendation, I get this error now:
Msg 515, Level 16, State 2, Line 263
Cannot insert the value NULL into column 'Diagnosis', table 'tempdb.dbo.#Summary2_____
The statement has been terminated.
So Jeff.. what result are you wanting to have returned when you pass a string value of 100.81 to your Integer field?
If the answer is 100, then use the syntax from my second select statement. The first statement demonstrates the error that you are receiving. The other two demonstrate different ways to deal with the error:
Either truncate the number, pass it in as a decimal, or change the datatype for the receiving field to decimal.
If the answer is 100, then use the syntax from my second select statement. The first statement demonstrates the error that you are receiving. The other two demonstrate different ways to deal with the error:
Either truncate the number, pass it in as a decimal, or change the datatype for the receiving field to decimal.
You can use ISNULL to check for null values.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath 123 -
Msg 8114, Level 16, State 5, Line 195
Error converting data type varchar to numeric.
Msg 8114, Level 16, State 5, Line 195
Error converting data type varchar to numeric.
could you run this and check how many non-numeric DiagnosisCodes you have?
select DiagnosisCode from uvVisitDiagnosis where ISNUMERIC(DiagnosisCode) = 0
ASKER
Split pts to be fair. Ill need to rethink this all. Thanks for trying.
Try running each of the statements below in query analyzer. The first is a decimal value stored as a string. The second looks for the decimal point in the string and truncates the string at the decimal point. The third converts from a deciaml value rather than a string value:
Select convert(int,'100.81')
Select convert(int,Left('100.81',
Select convert(int,100.81)