Help Conversion failed error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Conversion failed error

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-07-19 : 05:49:06

Hi guys i am running this query below and getting the following error message 'Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M052' to data type int' can anyone help me with this? i think its the MSK Code its a nvarchar how can i change that into an int?

SELECT DISTINCT
dbo.Create_Spells_Diagnosis_Dataset.AdmittedPatientEpisodeKey, dbo.Create_Spells_Diagnosis_Dataset.AgeAtCDSActivityDate,
dbo.Create_Spells_Diagnosis_Dataset.End_Date_of_Episode, dbo.Create_Spells_Diagnosis_Dataset.Start_of_Episode,
dbo.Create_Spells_Diagnosis_Dataset.EpisodeDuration, dbo.Create_Spells_Diagnosis_Dataset.AdmissionMethodCode,
dbo.Create_Spells_Diagnosis_Dataset.AdmissionMethodSubType, dbo.Create_Spells_Diagnosis_Dataset.GP_Code,
dbo.Create_Spells_Diagnosis_Dataset.MONTH_YEAR, dbo.Create_Spells_Diagnosis_Dataset.NHSOrganisationCode,
dbo.Create_Spells_Diagnosis_Dataset.NHSOrganisationOrganisationName, dbo.Create_Spells_Diagnosis_Dataset.PCT_Code,
dbo.Create_Spells_Diagnosis_Dataset.PCT_Name, dbo.Create_Spells_Diagnosis_Dataset.GP_Name,
dbo.Create_Spells_Diagnosis_Dataset.Primary_Diagnosis, dbo.Create_Spells_Diagnosis_Dataset.[1st Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[2nd Secondary], dbo.Create_Spells_Diagnosis_Dataset.[3rd Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[4th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[5th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[6th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[7th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[8th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[9th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[10th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[11th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[12th Secondary], dbo.Create_Spells_Diagnosis_Dataset.HospitalProviderSpellNumber,
dbo.Create_Spells_Diagnosis_Dataset.PatientKey, dbo.Create_Spells_Diagnosis_Dataset.MDG,
dbo.Create_Spells_Diagnosis_Dataset.[Admissions Baseline], dbo.Create_Spells_Diagnosis_Dataset.Status,
CASE PatientConsentFlag WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Consent, dbo.Create_Spells_Diagnosis_Dataset.HRGKey,
dbo.Create_Spells_Diagnosis_Dataset.CoreHRGCalculatedKey, dbo.Create_Spells_Diagnosis_Dataset.FinalHRG,
dbo.Create_Spells_Diagnosis_Dataset.HRGName, dbo.MSK.[MSK Name]
FROM dbo.Create_Spells_Diagnosis_Dataset INNER JOIN
dbo.MSK ON dbo.Create_Spells_Diagnosis_Dataset.Primary_Diagnosis = dbo.MSK.[MSK Code] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[1st Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[2nd Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[3rd Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[4th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[5th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[6th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[7th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[8th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[9th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[10th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[11th Secondary] OR
dbo.MSK.[MSK Code] = dbo.Create_Spells_Diagnosis_Dataset.[12th Secondary]

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-19 : 05:57:27

you can't change it into a int. 'M' is not a number :p

but you can change the int into a nvarchar:

dbo.MSK.[MSK Code] = CONVERT(NVARCHAR(??), dbo.Create_Spells_Diagnosis_Dataset.[XXth Secondary])

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-07-19 : 06:01:43

Thanks mate let me try this

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-07-19 : 06:02:54

where in the query do i but the above code you suggested?

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-19 : 06:29:57

SELECT DISTINCT
dbo.Create_Spells_Diagnosis_Dataset.AdmittedPatientEpisodeKey, dbo.Create_Spells_Diagnosis_Dataset.AgeAtCDSActivityDate,
dbo.Create_Spells_Diagnosis_Dataset.End_Date_of_Episode, dbo.Create_Spells_Diagnosis_Dataset.Start_of_Episode,
dbo.Create_Spells_Diagnosis_Dataset.EpisodeDuration, dbo.Create_Spells_Diagnosis_Dataset.AdmissionMethodCode,
dbo.Create_Spells_Diagnosis_Dataset.AdmissionMethodSubType, dbo.Create_Spells_Diagnosis_Dataset.GP_Code,
dbo.Create_Spells_Diagnosis_Dataset.MONTH_YEAR, dbo.Create_Spells_Diagnosis_Dataset.NHSOrganisationCode,
dbo.Create_Spells_Diagnosis_Dataset.NHSOrganisationOrganisationName, dbo.Create_Spells_Diagnosis_Dataset.PCT_Code,
dbo.Create_Spells_Diagnosis_Dataset.PCT_Name, dbo.Create_Spells_Diagnosis_Dataset.GP_Name,
dbo.Create_Spells_Diagnosis_Dataset.Primary_Diagnosis, dbo.Create_Spells_Diagnosis_Dataset.[1st Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[2nd Secondary], dbo.Create_Spells_Diagnosis_Dataset.[3rd Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[4th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[5th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[6th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[7th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[8th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[9th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[10th Secondary], dbo.Create_Spells_Diagnosis_Dataset.[11th Secondary],
dbo.Create_Spells_Diagnosis_Dataset.[12th Secondary], dbo.Create_Spells_Diagnosis_Dataset.HospitalProviderSpellNumber,
dbo.Create_Spells_Diagnosis_Dataset.PatientKey, dbo.Create_Spells_Diagnosis_Dataset.MDG,
dbo.Create_Spells_Diagnosis_Dataset.[Admissions Baseline], dbo.Create_Spells_Diagnosis_Dataset.Status,
CASE PatientConsentFlag WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Consent, dbo.Create_Spells_Diagnosis_Dataset.HRGKey,
dbo.Create_Spells_Diagnosis_Dataset.CoreHRGCalculatedKey, dbo.Create_Spells_Diagnosis_Dataset.FinalHRG,
dbo.Create_Spells_Diagnosis_Dataset.HRGName, dbo.MSK.[MSK Name]
FROM dbo.Create_Spells_Diagnosis_Dataset INNER JOIN
dbo.MSK ON CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.Primary_Diagnosis) = dbo.MSK.[MSK Code] OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[1st Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[2nd Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[3rd Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[4th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[5th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[6th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[7th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[8th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[9th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[10th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[11th Secondary]) OR
dbo.MSK.[MSK Code] = CONVERT(nvarchar(50), dbo.Create_Spells_Diagnosis_Dataset.[12th Secondary])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 11:30:37

looks like a typical unpivot scenario to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources