| Author |
 Topic  |
|
|
w1102157
Yak Posting Veteran
80 Posts |
 Posted - 07/19/2012 : 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
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/19/2012 : 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 - 07/19/2012 : 06:01:43
|
| Thanks mate let me try this |
|
|
|
w1102157
Yak Posting Veteran
80 Posts |
Posted - 07/19/2012 : 06:02:54
|
| where in the query do i but the above code you suggested? |
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/19/2012 : 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
India
47040 Posts |
Posted - 07/19/2012 : 11:30:37
|
looks like a typical unpivot scenario to me
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Help Conversion failed error
