Sql Server View : Conversion failed when converting the varchar value 'No PhysicalAttributeID' to data type int

Question

So I have created a view, but I seem to be getting an error I do not seem to understand why, I am just assigning the value, I am posting the code here

SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN commodityid = 1 THEN 0 ELSE 1 END) AS ID,
       CommodityID,
       CommodityName,
       SubCommodityID,
       SubCommodityName,
       SectorID,
       Sector,
       GroupID,
       GroupName,
       StatisticID,
       StatisticType,
       SourceID,
       Source,
       SourceDescription,
       PhysicalAttributeID,
       PhysicalAttribute,
       UtilizationID,
       UtilizationPractice,
       ProductionPracticeID,
       ProductionPractice,
       Description,
       SourceSeriesID,
       TimeID,
       TimeFrequency,
       Date,
       GeographyID,
       GeographyType,
       City,
       County,
       State,
       Region,
       Country,
       UnitID,
       Unit,
       LifecyclePhaseID,
       LifecyclePhaseDescription AS Value
FROM (SELECT DISTINCT
             dv.ERSDataValues_ERSCommodity_ID AS CommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN SUBSTRING(csc.ERSCommoditySubCommodity_Desc, 1, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) - 1)
                  ELSE csc.ERSCommoditySubCommodity_Desc
             END AS CommodityName,
             cds.ERSCommoditySubCommodity_ID AS SubCommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN LTRIM(SUBSTRING(csc.ERSCommoditySubCommodity_Desc, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) + 1, LEN(csc.ERSCommoditySubCommodity_Desc)))
                  ELSE 'No SubCommodity'
             END AS SubCommodityName,
             su.ERSSector_ID AS SectorID,
             su.ERSSector_Desc AS Sector,
             gu.ERSGroup_ID AS GroupID,
             gu.ERSGroup_Desc AS GroupName,
             stu.ERSStatisticType_ID AS StatisticID,
             stu.ERSStatisticType_Attribute AS StatisticType,
             slu.ERSSource_ID AS SourceID,
             slu.ERSSource_Desc AS Source,
             slu.ERSSource_LongDesc AS SourceDescription,
             phlu.ERSPhysicalAttribute_ID AS PhysicalAttributeID,
             cds.ERSCommodity_PhysicalAttribute_Desc AS PhysicalAttribute,
             upu.ERSUtilPractice_ID AS UtilizationID,
             upu.ERSUtilPractice_Desc AS UtilizationPractice,
             pu.ERSProdPractice_ID AS ProductionPracticeID,
             pu.ERSProdPractice_Desc AS ProductionPractice,
             CASE
                  WHEN cds.ERSCommodity_SourceSeriesID_LongDesc IS NULL THEN 'No Description'
                  ELSE cds.ERSCommodity_SourceSeriesID_LongDesc
             END AS Description,
             CASE
                  WHEN cds.ERSCommodity_SourceSeriesID IS NULL THEN 'No SourceSeriesID'
                  ELSE cds.ERSCommodity_SourceSeriesID
             END AS SourceSeriesID,
             tu.ERSTimeDimension_ID AS TimeID,
             REPLACE(tdt.ERSTimeDimensionType_Desc, 'ERS', '') AS TimeFrequency,
             tu.ERSTimeDimension_Date AS Date,
             gdu.ERSGeographyDimension_ID AS GeographyID,
             gtu.ERSGeographyType_Desc AS GeographyType,
             CASE
                  WHEN COALESCE(gdu.ERSGeographyDimension_City, '') = '' THEN 'No City'
                  ELSE gdu.ERSGeographyDimension_City
             END AS City,
             CASE
                  WHEN COALESCE(gdu.ERSGeographyDimension_County, '') = '' THEN 'No County'
                  ELSE gdu.ERSGeographyDimension_County
             END AS County,
             CASE
                  WHEN COALESCE(gdu.ERSGeographyDimension_State, '') = '' THEN 'No State'
                  ELSE gdu.ERSGeographyDimension_State
             END AS State,
             CASE
                  WHEN COALESCE(gdu.ERSGeographyDimension_Region, '') = '' THEN 'No Region'
                  ELSE gdu.ERSGeographyDimension_Region
             END AS Region,
             CASE
                  WHEN COALESCE(gdu.ERSGeographyDimension_Country, '') = '' THEN 'No Country'
                  ELSE gdu.ERSGeographyDimension_Country
             END AS Country,
             ulu.ERSUnit_ID AS UnitID,
             ulu.ERSUnit_Desc AS Unit,
             dv.ERSDataValues_DataRowLifecyclePhaseID AS LifecyclePhaseID,
             dlu.ERSDataLifecyclePhase_Desc AS LifecyclePhaseDescription,
             dv.ERSDataValues_AttributeValue AS Value
      FROM CoSD.ERSCommodityDataSeries cds
           INNER JOIN cosd.ERSPhysicalAttribute_LU phlu ON phlu.ERSPhysicalAttribute_ID = cds.ERSCommodity_ERSPhysicalAttribute_ID
           INNER JOIN CoSD.ERSDataValues dv ON cds.ERSCommodity_ID = dv.ERSDataValues_ERSCommodity_ID
           INNER JOIN CoSD.ERSSector_LU su ON cds.ERSCommodity_ERSSector_ID = su.ERSSector_ID
           INNER JOIN CoSD.ERSGroup_LU gu ON cds.ERSCommodity_ERSGroup_ID = gu.ERSGroup_ID
           INNER JOIN CoSD.ERSProdPractice_LU pu ON cds.ERSCommodity_ERSProdPractice_ID = pu.ERSProdPractice_ID
           INNER JOIN CoSD.ERSUtilPractice_LU upu ON cds.ERSCommodity_ERSUtilPractice_ID = upu.ERSUtilPractice_ID
                                                 AND cds.ERSCommodity_ERSUtilPractice_ID = upu.ERSUtilPractice_ID
           INNER JOIN CoSD.ERSUnit_LU ulu ON dv.ERSDataValues_ERSUnit_ID = ulu.ERSUnit_ID
           INNER JOIN CoSD.ERSSource_LU slu ON cds.ERSCommodity_ERSSource_ID = slu.ERSSource_ID
           INNER JOIN CoSD.ERSStatisticType_LU stu ON cds.ERSCommodity_ERSStatisticType_ID = stu.ERSStatisticType_ID
           INNER JOIN CoSD.ERSTimeDimension_LU tu ON dv.ERSDataValues_ERSTimeDimension_ID = tu.ERSTimeDimension_ID
           INNER JOIN CoSD.ERSGeographyDimension_LU gdu ON dv.ERSDataValues_ERSGeography_ID = gdu.ERSGeographyDimension_ID
           INNER JOIN CoSD.ERSTimeDimensionType_LU tdt ON tu.ERSTimeDimension_TimeDimensionType_ID = tdt.ERSTimeDimensionType_ID
           INNER JOIN CoSD.ERSGeographyType_LU gtu ON gdu.ERSGeographyDimension_ERSGeographyType_ID = gtu.ERSGeographyType_ID
           INNER JOIN CoSD.ERSCommoditySubCommodity_LU csc ON csc.ERSCommoditySubCommodity_ID = cds.ERSCommoditySubCommodity_ID
           INNER JOIN cosd.ERSDataLifecycle_LU dlu ON dlu.ERSDataLifecyclePhase_ID = dv.ERSDataValues_DataRowLifecyclePhaseID
      WHERE dv.ERSDataRowPrivacy_ID = 1
        AND dv.ERSDataValues_AttributeValue IS NOT NULL
      UNION ALL
      SELECT DISTINCT
             cds.ERSCommodity_ID AS CommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN SUBSTRING(csc.ERSCommoditySubCommodity_Desc, 1, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) - 1)
                  ELSE csc.ERSCommoditySubCommodity_Desc
             END AS CommodityName,
             csc.ERSCommoditySubCommodity_ID AS SubCommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN LTRIM(SUBSTRING(csc.ERSCommoditySubCommodity_Desc, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) + 1, LEN(csc.ERSCommoditySubCommodity_Desc)))
                  ELSE 'No SubCommodity'
             END AS SubCommodityName,
             slu.ERSSector_ID AS SectorID,
             slu.ERSSector_Desc AS Sector,
             glu.ERSGroup_ID AS GroupID,
             glu.ERSGroup_Desc AS GroupName,
             stu.ERSStatisticType_ID AS StatisticID,
             stu.ERSStatisticType_Attribute AS StatisticType,
             selu.ERSSource_ID AS SourceID,
             cvo.ERSConstructedVariable_InputSources AS Source,
             selu.ERSSource_LongDesc AS SourceDescription,
             cds.ERSCommodity_ERSPhysicalAttribute_ID AS PhysicalAttributeID,
             cds.ERSCommodity_PhysicalAttribute_Desc AS PhysicalAttribute,
             ulu.ERSUtilPractice_ID AS UtilizationID,
             ulu.ERSUtilPractice_Desc AS UtilizationPractice,
             plu.ERSProdPractice_ID AS ProductionPracticeID,
             plu.ERSProdPractice_Desc AS ProductionPractice,
             cvo.ERSConstructedVariable_OutputName AS Description,
             CASE
                  WHEN cds.ERSCommodity_SourceSeriesID LIKE '%(N%' THEN 'No SourceSeriesID'
                  ELSE cds.ERSCommodity_SourceSeriesID
             END AS SourceSeriesID,
             tlu.ERSTimeDimension_ID AS TimeID,
             REPLACE(tdlu.ERSTimeDimensionType_Desc, 'ERS', '') AS TimeFrequency,
             cvo.ERSConstructedVariable_TimeDimensionDate AS Date,
             gdlu.ERSGeographyDimension_ID AS GeographyID,
             gtlu.ERSGeographyType_Desc AS GeographyType,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_City, '') = '' THEN 'No City'
                  ELSE gdlu.ERSGeographyDimension_City
             END AS City,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_County, '') = '' THEN 'No County'
                  ELSE gdlu.ERSGeographyDimension_County
             END AS County,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_State, '') = '' THEN 'No State'
                  ELSE gdlu.ERSGeographyDimension_State
             END AS State,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_Region, '') = '' THEN 'No Region'
                  ELSE gdlu.ERSGeographyDimension_Region
             END AS Region,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_Country, '') = '' THEN 'No Country'
                  ELSE gdlu.ERSGeographyDimension_Country
             END AS Country,
             unlu.ERSUnit_ID AS UnitID,
             unlu.ERSUnit_Desc AS Unit,
             cvo.ERSConstructedVariable_DataRowLifecyclePhaseID AS LifecyclePhaseID,
             dlu.ERSDataLifecyclePhase_Desc AS LifecyclePhaseDescription,
             cvo.ERSConstructedVariable_OutputValue AS Value
      FROM CoSD.ERSConstructedVariablesOutcomes cvo
           INNER JOIN CoSD.ERSCommodityDataSeries cds ON cvo.ERSConstructedVariable_NewDataSeriesID = cds.ERSCommodity_ID
           INNER JOIN CoSD.ERSSector_LU slu ON cds.ERSCommodity_ERSSector_ID = slu.ERSSector_ID
           INNER JOIN CoSD.ERSGroup_LU glu ON cds.ERSCommodity_ERSGroup_ID = glu.ERSGroup_ID
           INNER JOIN CoSD.ERSCommoditySubCommodity_LU csc ON cds.ERSCommoditySubCommodity_ID = csc.ERSCommoditySubCommodity_ID
           INNER JOIN CoSD.ERSStatisticType_LU stu ON cds.ERSCommodity_ERSStatisticType_ID = stu.ERSStatisticType_ID
           INNER JOIN CoSD.ERSUtilPractice_LU ulu ON cds.ERSCommodity_ERSUtilPractice_ID = ulu.ERSUtilPractice_ID
           INNER JOIN CoSD.ERSProdPractice_LU plu ON cds.ERSCommodity_ERSProdPractice_ID = plu.ERSProdPractice_ID
           INNER JOIN CoSD.ERSGeographyDimension_LU gdlu ON cvo.ERSConstructedVariable_OutputGeographyDimensionID = gdlu.ERSGeographyDimension_ID
           INNER JOIN CoSD.ERSUnit_LU unlu ON cvo.ERSConstructedVariable_OutputUnitID = unlu.ERSUnit_ID
           INNER JOIN CoSD.ERSGeographyType_LU gtlu ON gdlu.ERSGeographyDimension_ERSGeographyType_ID = gtlu.ERSGeographyType_ID
           INNER JOIN cosd.ERSTimeDimension_LU tlu ON tlu.ERSTimeDimension_ID = cvo.ERSConstructedVariable_TimeDimensionID
                                                  AND tlu.ERSTimeDimension_Date = cvo.ERSConstructedVariable_TimeDimensionDate
                                                  AND YEAR(tlu.ERSTimeDimension_Date) = YEAR(cvo.ERSConstructedVariable_TimeDimensionDate)
                                                  AND MONTH(tlu.ERSTimeDimension_Date) = MONTH(ERSConstructedVariable_TimeDimensionDate)
           INNER JOIN cosd.ERSTimeDimensionType_LU tdlu ON tdlu.ERSTimeDimensionType_ID = tlu.ERSTimeDimension_TimeDimensionType_ID
           INNER JOIN cosd.ERSSource_LU selu ON cvo.ERSConstructedVariable_InputSourceID = selu.ERSSource_ID
           INNER JOIN cosd.ERSDataLifecycle_LU dlu ON dlu.ERSDataLifecyclePhase_ID = cvo.ERSConstructedVariable_DataRowLifecyclePhaseID
      WHERE cvo.ERSConstructedVariable_DataRowPrivacyID = 1
        AND cvo.ERSConstructedVariable_NewDataSeriesID IS NOT NULL
        AND cvo.ERSConstructedVariable_OutputValue IS NOT NULL
      UNION ALL
      SELECT DISTINCT
             CDS.ERSCommodity_ID AS CommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN SUBSTRING(csc.ERSCommoditySubCommodity_Desc, 1, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) - 1)
                  ELSE csc.ERSCommoditySubCommodity_Desc
             END AS CommodityName,
             csc.ERSCommoditySubCommodity_ID AS SubCommodityID,
             CASE
                  WHEN CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) > 0 THEN LTRIM(SUBSTRING(csc.ERSCommoditySubCommodity_Desc, CHARINDEX(',', csc.ERSCommoditySubCommodity_Desc) + 1, LEN(csc.ERSCommoditySubCommodity_Desc)))
                  ELSE 'No SubCommodity'
             END AS SubCommodityName,
             slu.ERSSector_ID AS SectorID,
             slu.ERSSector_Desc AS Sector,
             glu.ERSGroup_ID AS GroupID,
             glu.ERSGroup_Desc AS GroupName,
             stu.ERSStatisticType_ID AS StatisticID,
             stu.ERSStatisticType_Attribute AS StatisticType,
             selu.ERSSource_ID AS SourceID,
             cvo.ERSConstructedVariable_InputSources AS Source,
             selu.ERSSource_LongDesc AS SourceDescription,
             CDS.ERSCommodity_ERSPhysicalAttribute_ID AS PhysicalAttributeID,
             CDS.ERSCommodity_PhysicalAttribute_Desc AS PhysicalAttribute,
             ulu.ERSUtilPractice_ID AS UtilizationID,
             ulu.ERSUtilPractice_Desc AS UtilizationPractice,
             plu.ERSProdPractice_ID AS ProductionPracticeID,
             plu.ERSProdPractice_Desc AS ProductionPractice,
             cvo.ERSConstructedVariable_OutputName AS Description,
             CASE
                  WHEN CDS.ERSCommodity_SourceSeriesID IS NULL THEN 'No SourceSeriesID'
                  ELSE CDS.ERSCommodity_SourceSeriesID
             END AS SourceSeriesID,
             tlu.ERSTimeDimension_ID AS TimeID,
             REPLACE(tdlu.ERSTimeDimensionType_Desc, 'ERS', '') AS TimeFrequency,
             cvo.ERSConstructedVariable_TimeDimensionDate AS Date,
             gdlu.ERSGeographyDimension_ID AS GeographyID,
             gtlu.ERSGeographyType_Desc AS GeographyType,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_City, '') = '' THEN 'No City'
                  ELSE gdlu.ERSGeographyDimension_City
             END AS City,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_County, '') = '' THEN 'No County'
                  ELSE gdlu.ERSGeographyDimension_County
             END AS County,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_State, '') = '' THEN 'No State'
                  ELSE gdlu.ERSGeographyDimension_State
             END AS State,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_Region, '') = '' THEN 'No Region'
                  ELSE gdlu.ERSGeographyDimension_Region
             END AS Region,
             CASE
                  WHEN COALESCE(gdlu.ERSGeographyDimension_Country, '') = '' THEN 'No Country'
                  ELSE gdlu.ERSGeographyDimension_Country
             END AS Country,
             unlu.ERSUnit_ID AS UnitID,
             unlu.ERSUnit_Desc AS Unit,
             cvo.ERSConstructedVariable_DataRowLifecyclePhaseID AS LifecyclePhaseID,
             dlu.ERSDataLifecyclePhase_Desc AS LifecyclePhaseDescription,
             cvo.ERSConstructedVariable_OutputValue AS Value
      FROM CoSD.ERSConstructedVariablesOutcomes cvo
           INNER JOIN CoSD.ERSBusinessLogic BL ON cvo.ERSConstructedVariable_BusinessLogicID = BL.ERSBusinessLogic_ID
           INNER JOIN cosd.ERSCommodityDataSeries CDS ON CDS.ERSCommodity_ID = BL.ERSBusinessLogic_InputDataSeries
           INNER JOIN CoSD.ERSSector_LU slu ON CDS.ERSCommodity_ERSSector_ID = slu.ERSSector_ID
           INNER JOIN CoSD.ERSGroup_LU glu ON CDS.ERSCommodity_ERSGroup_ID = glu.ERSGroup_ID
           INNER JOIN CoSD.ERSCommoditySubCommodity_LU csc ON CDS.ERSCommoditySubCommodity_ID = csc.ERSCommoditySubCommodity_ID
           INNER JOIN CoSD.ERSStatisticType_LU stu ON CDS.ERSCommodity_ERSStatisticType_ID = stu.ERSStatisticType_ID
           INNER JOIN CoSD.ERSUtilPractice_LU ulu ON CDS.ERSCommodity_ERSUtilPractice_ID = ulu.ERSUtilPractice_ID
           INNER JOIN CoSD.ERSProdPractice_LU plu ON CDS.ERSCommodity_ERSProdPractice_ID = plu.ERSProdPractice_ID
           INNER JOIN CoSD.ERSGeographyDimension_LU gdlu ON cvo.ERSConstructedVariable_OutputGeographyDimensionID = gdlu.ERSGeographyDimension_ID
           INNER JOIN CoSD.ERSUnit_LU unlu ON cvo.ERSConstructedVariable_OutputUnitID = unlu.ERSUnit_ID
           INNER JOIN CoSD.ERSGeographyType_LU gtlu ON gdlu.ERSGeographyDimension_ERSGeographyType_ID = gtlu.ERSGeographyType_ID
           INNER JOIN cosd.ERSTimeDimension_LU tlu ON tlu.ERSTimeDimension_ID = cvo.ERSConstructedVariable_TimeDimensionID
                                                  AND tlu.ERSTimeDimension_Date = cvo.ERSConstructedVariable_TimeDimensionDate
                                                  AND YEAR(tlu.ERSTimeDimension_Date) = YEAR(cvo.ERSConstructedVariable_TimeDimensionDate)
                                                  AND MONTH(tlu.ERSTimeDimension_Date) = MONTH(ERSConstructedVariable_TimeDimensionDate)
           INNER JOIN cosd.ERSTimeDimensionType_LU tdlu ON tdlu.ERSTimeDimensionType_ID = tlu.ERSTimeDimension_TimeDimensionType_ID
           INNER JOIN cosd.ERSSource_LU selu ON cvo.ERSConstructedVariable_InputSourceID = selu.ERSSource_ID
           INNER JOIN cosd.ERSDataLifecycle_LU dlu ON dlu.ERSDataLifecyclePhase_ID = cvo.ERSConstructedVariable_DataRowLifecyclePhaseID
      WHERE cvo.ERSConstructedVariable_DataRowPrivacyID = 1
        AND BL.ERSBusinessLogic_InputsCount = 1
        AND BL.ERSBusinessLogic_InputDataSeries NOT LIKE '%CV%'
        AND cvo.ERSConstructedVariable_NewDataSeriesID IS NULL
        AND cvo.ERSConstructedVariable_OutputValue IS NOT NULL
      UNION ALL
      SELECT DISTINCT
             '-1' AS CommodityID,
             ERSMacro_Desc AS CommodityName,
             '-1' AS SubCommodityID,
             ERSMacro_LongDesc AS SubCommodityName,
             '4' AS SectorID,
             'Macro' AS Sector,
             '17' AS GroupID,
             'Macro' AS GroupName,
             NULL AS StatisticID,
             'No StatisticType' AS StatisticType,
             slu.ERSSource_ID AS SourceID,
             slu.ERSSource_Desc AS Source,
             slu.ERSSource_LongDesc AS SourceDescription,
             TRY_CONVERT(varchar, 'No PhysicalAttributeID') AS PhysicalAttributeID,
             'No PhysicalAttribute' AS PhysicalAttribute,
             'No UtilizationID' AS UtilizationID,
             'No UtilizationPractice' AS UtilizationPractice,
             'No ProductionPracticeID' AS ProductionPracticeID,
             'No ProductionPractice' AS ProductionPractice,
             'No Description' AS Description,
             'No SourceSeriesID' AS SourceSeriesID,
             tlu.ERSTimeDimension_ID AS TimeID,
             REPLACE(ttlu.ERSTimeDimensionType_Desc, 'ERS', '') AS TimeFrequency,
             tlu.ERSTimeDimension_Date AS Date,
             glu.ERSGeographyDimension_ID AS GeographyID,
             gtlu.ERSGeographyType_Desc AS GeographyType,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_City, '') = '' THEN 'No City'
                  ELSE glu.ERSGeographyDimension_City
             END AS City,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_County, '') = '' THEN 'No County'
                  ELSE glu.ERSGeographyDimension_County
             END AS County,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_State, '') = '' THEN 'No State'
                  ELSE glu.ERSGeographyDimension_State
             END AS State,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_Region, '') = '' THEN 'No Region'
                  ELSE glu.ERSGeographyDimension_Region
             END AS Region,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_Country, '') = '' THEN 'No Country'
                  ELSE glu.ERSGeographyDimension_Country
             END AS Country,
             ulu.ERSUnit_ID AS UnitID,
             ulu.ERSUnit_Desc AS Unit,
             'No LifecyclephaseID' AS LifecyclePhaseID,
             'No LifecyclePhaseDescription' AS LifecyclePhaseDescription,
             mlu.ERSMacro_Value AS Value
      FROM cosd.ERSMacro_LU mlu
           INNER JOIN cosd.ERSSource_LU slu ON slu.ERSSource_ID = mlu.ERSMacro_Source_ID
           INNER JOIN cosd.ERSTimeDimension_LU tlu ON tlu.ERSTimeDimension_ID = mlu.ERSMacro_TimeDimension_ID
           INNER JOIN cosd.ERSTimeDimensionType_LU ttlu ON ttlu.ERSTimeDimensionType_ID = tlu.ERSTimeDimension_TimeDimensionType_ID
           INNER JOIN cosd.ERSGeographyDimension_LU glu ON glu.ERSGeographyDimension_ID = mlu.ERSMacro_GeographyDimension_ID
           INNER JOIN cosd.ERSGeographyType_LU gtlu ON gtlu.ERSGeographyType_ID = glu.ERSGeographyDimension_ERSGeographyType_ID
           INNER JOIN cosd.ERSUnit_LU ulu ON ulu.ERSUnit_ID = mlu.ERSMacro_Unit_ID) derived;

I get this error :

Conversion failed when converting the varchar value 'No PhysicalAttributeID' to data type int.

But the best part is if I run the last part of the code as below

SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN CommodityID = 1 THEN 0 ELSE 1 END) AS ID,
       CommodityID,
       CommodityName,
       SubCommodityID,
       SubCommodityName,
       SectorID,
       Sector,
       GroupID,
       GroupName,
       StatisticID,
       StatisticType,
       SourceID,
       Source,
       SourceDescription,
       PhysicalAttributeID,
       PhysicalAttribute,
       UtilizationID,
       UtilizationPractice,
       ProductionPracticeID,
       ProductionPractice,
       Description,
       SourceSeriesID,
       TimeID,
       TimeFrequency,
       Date,
       GeographyID,
       GeographyType,
       City,
       County,
       State,
       Region,
       Country,
       UnitID,
       Unit,
       LifecyclePhaseID,
       LifecyclePhaseDescription,
       Value
FROM (SELECT DISTINCT
             '-1' AS CommodityID,
             ERSMacro_Desc AS CommodityName,
             '-1' AS SubCommodityID,
             ERSMacro_LongDesc AS SubCommodityName,
             '4' AS SectorID,
             'Macro' AS Sector,
             '17' AS GroupID,
             'Macro' AS GroupName,
             'No Statistic' AS StatisticID,
             'No StatisticType' AS StatisticType,
             slu.ERSSource_ID AS SourceID,
             slu.ERSSource_Desc AS Source,
             slu.ERSSource_LongDesc AS SourceDescription,
             'No PhysicalAttributeID' AS PhysicalAttributeID,
             'No PhysicalAttribute' AS PhysicalAttribute,
             'No UtilizationID' AS UtilizationID,
             'No UtilizationPractice' AS UtilizationPractice,
             'No ProductionPracticeID' AS ProductionPracticeID,
             'No ProductionPractice' AS ProductionPractice,
             'No Description' AS Description,
             'No SourceSeriesID' AS SourceSeriesID,
             tlu.ERSTimeDimension_ID AS TimeID,
             REPLACE(ttlu.ERSTimeDimensionType_Desc, 'ERS', '') AS TimeFrequency,
             tlu.ERSTimeDimension_Date AS Date,
             glu.ERSGeographyDimension_ID AS GeographyID,
             gtlu.ERSGeographyType_Desc AS GeographyType,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_City, '') = '' THEN 'No City'
                  ELSE glu.ERSGeographyDimension_City
             END AS City,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_County, '') = '' THEN 'No County'
                  ELSE glu.ERSGeographyDimension_County
             END AS County,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_State, '') = '' THEN 'No State'
                  ELSE glu.ERSGeographyDimension_State
             END AS State,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_Region, '') = '' THEN 'No Region'
                  ELSE glu.ERSGeographyDimension_Region
             END AS Region,
             CASE
                  WHEN COALESCE(glu.ERSGeographyDimension_Country, '') = '' THEN 'No Country'
                  ELSE glu.ERSGeographyDimension_Country
             END AS Country,
             ulu.ERSUnit_ID AS UnitID,
             ulu.ERSUnit_Desc AS Unit,
             'No LifecyclephaseID' AS LifecyclePhaseID,
             'No LifecyclePhaseDescription' AS LifecyclePhaseDescription,
             mlu.ERSMacro_Value AS Value
      FROM cosd.ERSMacro_LU mlu
           INNER JOIN cosd.ERSSource_LU slu ON slu.ERSSource_ID = mlu.ERSMacro_Source_ID
           INNER JOIN cosd.ERSTimeDimension_LU tlu ON tlu.ERSTimeDimension_ID = mlu.ERSMacro_TimeDimension_ID
           INNER JOIN cosd.ERSTimeDimensionType_LU ttlu ON ttlu.ERSTimeDimensionType_ID = tlu.ERSTimeDimension_TimeDimensionType_ID
           INNER JOIN cosd.ERSGeographyDimension_LU glu ON glu.ERSGeographyDimension_ID = mlu.ERSMacro_GeographyDimension_ID
           INNER JOIN cosd.ERSGeographyType_LU gtlu ON gtlu.ERSGeographyType_ID = glu.ERSGeographyDimension_ERSGeographyType_ID
           INNER JOIN cosd.ERSUnit_LU ulu ON ulu.ERSUnit_ID = mlu.ERSMacro_Unit_ID) derived;

This runs perfectly without any problems. If I change 'No PhysicalAttributeID' to null, it runs properly, I have tried this solution and also this

I seem to be heading no where, any ideas ?

Guessing you have a `JOIN` with a `varchar` and `int` column comparison. — Thom A, Mar 07 '19 at 16:49
But I am not getting any value from the join, I am hard coding the value to the column and its a view, we do not assign any value in view right or my understanding is wrong ? — Bharat Radhakrishnan, Mar 07 '19 at 16:52

score 1 · Answer 1 · answered Mar 07 '19 at 16:57

1

It is because in the first query column 15 is PhysicalAttributeID which is almost certainly an int. Then you attempt to use UNION ALL to your second query. It will attempt an implicit conversion and fail.

An easy solution would be to adjust the first query. You would need to something like on each of those queries.

PhysicalAttributeID = convert(varchar(20), PhysicalAttributeID)

answered Mar 07 '19 at 16:57

Sean Lange

33,028
3
25
40

I got one solution as per my requirement, I kept 0 for one value of column and -1 if another value. case when ERSMacro_Desc='GDP Deflators' then '-1' else 0 end AS PhysicalAttributeID – Bharat Radhakrishnan Mar 07 '19 at 17:09
Use else '0' or you just continuing the conversion problem. – Sean Lange Mar 07 '19 at 17:17
@BharatRadhakrishnan While it may seem "safe" to assume that your tables will not be populated with rows where your "special" value (0, -1, whatever you choose) will never exist, it is still an assumption. By making this assumption, you also reduce the domain of the datatype current used by the column by 50 percent. Some day you might need to make use of negative ID values - like when you reach the maximum value of that datatype and do not wish to change the schema. – SMor Mar 07 '19 at 18:32

score 0 · Answer 2 · answered Mar 07 '19 at 17:58

This is the line you have error at:

TRY_CONVERT(varchar, 'No PhysicalAttributeID') AS PhysicalAttributeID

this is incompatible with its matches, like

phlu.ERSPhysicalAttribute_ID AS PhysicalAttributeID

or

cds.ERSCommodity_ERSPhysicalAttribute_ID AS PhysicalAttributeID

or

CDS.ERSCommodity_ERSPhysicalAttribute_ID AS PhysicalAttributeID

You will need to convert the numeric values above into varchar to solve your problem.

Sql Server View : Conversion failed when converting the varchar value 'No PhysicalAttributeID' to data type int

2 Answers2