data conversion Error in update query
-
update InpatientMedStat set InpatientMedStat.PrincipalDiagnosisCode = ltrim(rtrim(rptpxa2.DCode)) from InpatientMedStat
INNER JOIN rptpxa2 ON InpatientMedStat.PatientAccountNumber = rptpxa2.PNO
WHERE (LEN(RTRIM(InpatientMedStat.PrincipalDiagnosisCode)) = 0) and (rptPXA2.DCode) = 1
I do not know why it give me error as
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'V45.81 ' to a column of data type int.
Here is my table definiation;
CREATE TABLE [dbo].[InpatientMedStatExport] (
[RecordType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedstatCustomerID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientAccountNumber] [char] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MedicalRecordNumber] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdmissionDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdmissionSourceCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdmissionTypeCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DischargeDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DischargeStatusCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientDOB] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientZipCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientTypeCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientStreet] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientCity] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttendingPhysicianID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDA] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDB] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDC] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDD] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDA] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDB] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalDiagnosisCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OtherDiagnosisCodes] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalProcedureCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OtherlProcedureCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalProcedureDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OtherProcedureDates] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperPhysID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperPhysIDA] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientEthnicity] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TotalCharges] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TotalCosts] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reimbursement] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClassCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDRG] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDRGDescription] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDCCodeAndDescription] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsurancePlanCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NursingUnitCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filler] [char] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[InpatientMedStat] (
[RecordType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedstatCustomerID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientAccountNumber] [char] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedicalRecordNumber] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmissionDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmissionSourceCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmissionTypeCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeStatusCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientDOB] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientZipCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientTypeCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientStreet] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientCity] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttendingPhysicianID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDA] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDB] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDC] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherPhysicianIDD] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDA] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDB] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoleCodePhysicianIDD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalDiagnosisCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherDiagnosisCodes] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalProcedureCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherlProcedureCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalProcedureDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherProcedureDates] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperPhysID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperPhysIDA] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientEthnicity] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalCharges] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalCosts] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reimbursement] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClassCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDRG] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDRGDescription] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDCCodeAndDescription] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsurancePlanCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NursingUnitCode] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filler] [char] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientLName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientFName] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
Can you post the definition of rptpxa2 which is referenced in the query? Your problem may be in the way you handle the rptpxa2.DCode column. In your query you treat it both as a character data type (when you use ltrim/rtrim) and as a numerical data type (when you compare it to 1 rather than the string '1').
-
You didn't provide any DDL for table named "rptpxa2".
What are the datatypes of columns rptpxa2.PNO and rptpxa2.DCode ?
-
I agree, most probably the problem is caused by missing quotes (although without DLL it is just a guess - but both posted tables use CHAR on all columns):
and (rptPXA2.DCode) = '1'
-
sorry. here is
CREATE TABLE [dbo].[rptpxa2] (
[PNO] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccommondateCharge] [float] NULL ,
[FClass] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeathIndicate] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Disposition] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalCharge] [float] NULL ,
[AncillaryCharge] [float] NULL ,
[ResponsibleAttedning] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zatPractitioner] [float] NULL ,
[AdmDate] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDate] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Practitioner] [float] NULL ,
[ProcedureDate] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcedureCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POrder] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOrder] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
Have you tried with '1' instead of 1?
Also check the execution plan for implicit_conversion which would confirm vladan's theory.
-
Sorry. I do not think I understand what you are talking about. Thx.
-
Select name from dbo.SysObjects WHERE XType = '1'
--0 row(s) affected
--perfectly normal
Select name from dbo.SysObjects WHERE XType = 1
--This crashes so bad that the execution plan is not even generated in QA
/*
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'S ' to a column of data type int.
*/
SET SHOWPLAN_ALL ON
GO
Select name from dbo.SysObjects WHERE XType = 1
GO
SET SHOWPLAN_ALL ON
/*
Select name from dbo.SysObjects WHERE XType = 1
|--Clustered Index Scan(OBJECT
[Documentation_Audit].[dbo].[sysobjects].[sysobjects]),WHERE
Convert([sysobjects].[xtype])=Convert([@1])))*/
Looks like I had the wrong operation name for this one. As you can see the execution plan added a convert operation so that the base column and the search argument have the same datatype. However converting a string to a int will almost invariably fail on a live system with correct datatyping in place
. -
after
update InpatientMedStat set InpatientMedStat.PrincipalDiagnosisCode = ltrim(rtrim(rptpxa2.DCode)) from InpatientMedStat INNER JOIN rptpxa2 ON InpatientMedStat.PatientAccountNumber = rptpxa2.PNO WHERE (LEN(RTRIM(InpatientMedStat.PrincipalDiagnosis 1 1 0 NULL NULL 1 NULL 6.5258541 NULL NULL NULL 3.6990247 NULL NULL UPDATE 0 NULL
|--Table Update(OBJECT
[Barb].[dbo].[InpatientMedStat]), SET[InpatientMedStat].[PrincipalDiagnosisCode]=[Expr1006])) 1 2 1 Table Update Update OBJECT[Barb].[dbo].[InpatientMedStat]), SET[InpatientMedStat].[PrincipalDiagnosisCode]=[Expr1006]) NULL 6.5258541 1.0847297E-2 6.5258537E-6 21 3.6990247 NULL NULL PLAN_ROW 0 1.0|--Compute Scalar(DEFINE
[Expr1006]=Convert(ltrim(rtrim([rptpxa2].[DCode]))))) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1006]=Convert(ltrim(rtrim([rptpxa2].[DCode])))) [Expr1006]=Convert(ltrim(rtrim([rptpxa2].[DCode]))) 6.5258541 0.0 6.5258541E-7 35 3.6881707 [Bmk1000], [Expr1006] NULL PLAN_ROW 0 1.0|--Top(ROWCOUNT est 0) 1 4 3 Top Top NULL NULL 6.5258541 0.0 6.5258541E-7 27 3.6881702 [Bmk1000], [rptpxa2].[DCode] NULL PLAN_ROW 0 1.0
|--Sort(DISTINCT ORDER BY
[Bmk1000] ASC)) 1 5 4 Sort Distinct Sort DISTINCT ORDER BY[Bmk1000] ASC) NULL 6.5258541 1.1261261E-2 5.8075227E-4 27 3.6881695 [Bmk1000], [rptpxa2].[DCode] NULL PLAN_ROW 0 1.0|--Hash Match(Inner Join, HASH
[rptpxa2].[PNO])=([Expr1007]), RESIDUAL[Expr1007]=[rptpxa2].[PNO])) 1 6 5 Hash Match Inner Join HASH[rptpxa2].[PNO])=([Expr1007]), RESIDUAL[Expr1007]=[rptpxa2].[PNO]) NULL 53.639999 0.0 0.02853955 45 3.6763275 [Bmk1000], [rptpxa2].[DCode] NULL PLAN_ROW 0 1.0|--Table Scan(OBJECT
[Barb].[dbo].[rptpxa2]), WHEREConvert([rptpxa2].[DCode])=1)) 1 7 6 Table Scan Table Scan OBJECT[Barb].[dbo].[rptpxa2]), WHEREConvert([rptpxa2].[DCode])=1) [rptpxa2].[PNO], [rptpxa2].[DCode] 53.639999 1.8857267 0.1210367 45 2.0067635 [rptpxa2].[PNO], [rptpxa2].[DCode] NULL PLAN_ROW 0 1.0|--Compute Scalar(DEFINE
[Expr1007]=Convert([InpatientMedStat].[PatientAccountNumber]))) 1 8 6 Compute Scalar Compute Scalar DEFINE[Expr1007]=Convert([InpatientMedStat].[PatientAccountNumber])) [Expr1007]=Convert([InpatientMedStat].[PatientAccountNumber]) 1439.3954 0.0 1.4393954E-4 40 1.5772436 [Bmk1000], [Expr1007] NULL PLAN_ROW 0 1.0|--Table Scan(OBJECT
[Barb].[dbo].[InpatientMedStat]), WHERElen(rtrim(Convert([InpatientMedStat].[PrincipalDiagnosisCode])))=0) ORDERED) 1 9 8 Table Scan Table Scan OBJECT[Barb].[dbo].[InpatientMedStat]), WHERElen(rtrim(Convert([InpatientMedStat].[PrincipalDiagnosisCode])))=0) ORDERED [Bmk1000], [InpatientMedStat].[PatientAccountNumber], [InpatientMedStat].[PrincipalDiagnosisCode] 1439.3954 1.5464674 0.0179557 866 1.5644231 [Bmk1000], [InpatientMedStat].[PatientAccountNumber], [InpatientMedStat].[PrincipalDiagnosisCode] NULL PLAN_ROW 0 1.0I run this
SET SHOWPLAN_ALL ON
go
update InpatientMedStat set InpatientMedStat.PrincipalDiagnosisCode = ltrim(rtrim(rptpxa2.DCode)) from InpatientMedStat
INNER JOIN rptpxa2 ON InpatientMedStat.PatientAccountNumber = rptpxa2.PNO
WHERE (LEN(RTRIM(InpatientMedStat.PrincipalDiagnosisCode)) = 0) and (rptPXA2.DCode) = 1
go
(9 row(s) affected)
Will you please explain more about that ? I am not sure about
SET SHOWPLAN_ALL ON
GO
Select name from dbo.SysObjects WHERE XType = 1
GO
SET SHOWPLAN_ALL ON
/*
Select name from dbo.SysObjects WHERE XType = 1
|--Clustered Index Scan(OBJECT
[Documentation_Audit].[dbo].[sysobjects].[sysobjects]),WHERE
Convert([sysobjects].[xtype])=Convert([@1])))*/
Looks like I had the wrong operation name for this one. As you can see the execution plan added a convert operation so that the base column and the search argument have the same datatype. However converting a string to a int will almost invariably fail on a live system with correct datatyping in place
-
Well first read this in books online :
data types-SQL Server, precedence
Basically the server must convert the data when the 2 sides of the operations have different datatypes. The convert choice is based on the precedence list I just provided from BOL.
EX try evaluation this
if 'tomato' = 1
==
if varchar = int --that's not possible so a convert is added to make the compaison possible
==
if convert(int, 'tomato') = 1
==
if int = int --equality check is now possible but...
==
convertion error -- this is the result of the execution of the conversion.
This is why we stronly suggest (best pratice) to make sure that both columns and or arguments of any operand are of the same datatype. IE WHERE INT = INT. Even something as sutle as BIT = INT might force a convert and turn an index seek into and index scan.
That's also why Vladan suggested to use where ColName = '1' (varchar) instead of 1 (int). You might also want to use a convert function to make it clearly visible to the humans that this value cannot be numeric.
-
I just completed my previous answer... Does that clear it up for you?
-
but in my case, it is the same data type.
-
I see at least 10 converts in your execution plan. So the server obviously thinks otherwise.
BTW varchar(9) <> nvarchar(9) <> [n]char(9).
Is there any collations differences between the tables or between the said columns?
-
[DCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrincipalDiagnosisCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
there are the only two fields I need to update.
I never used SHOWPLAN_ALL to analyze my query. Thank you very much.
I will be away this afternoon. I will be back tomorrow.
-
There's your answer...
(rptPXA2.DCode) = 1
==
Char(9) = int
===
see rest of my previous explanation for the error.
Once you try with '1' it'll work.
Good afternoon.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply