Select DISTINCT
		                            Acode Acode, 
		                             Image=( SELECT TOP 1 i.Url
					                    from dbo.ChromeImages i
					                    where i.StyleId = cs.StyleId
					                    and i.Country = cs.Country
					                    and i.Language = cs.Language
					                    AND i.BackgroundDescription='White'
					                    AND i.ShotCode='01'
					                    AND i.ImageType='V'
					                    AND i.Width=640 ),
		                            ModelYear Year, 
		                            MakeName DivDesc, 
		                            ModelName ModelDesc, 
		                            Trim RealTrim, 
		                            BestTrimName TrimDesc, 
                                CASE when CHARINDEX(cs.ModelName,cs.Trim)>0 THEN LTRIM(RTRIM(SUBSTRING(cs.Trim,LEN(cs.ModelName)+1,100))) ELSE cs.Trim END AS trim, 
		                            BodyType VarDesc,	                            
		                            Language LngCode, 
		                            MarketClass BodyStyle, 
		                            MSRP = BasePriceMSRP + BasePriceDestination + 28.00 from 
		                            dbo.ChromeStyles  cs
                            where 
		                            ModelId in (select DISTINCT ModelId from dbo.ChromeStyles where Acode='CAC70HOS031A0'and Language='EN') and Trim<>''and BodyType=''AND cs.makename = ''and Language='en' and Country='CA' and ModelYear =  Order By Trim, MSRPIncorrect syntax near the keyword 'Order'.