I work on sql server 2019 i face issue when run statment below
UPDATE FFFFF
SET ChemicalsMass= STUFF(
(
SELECT DISTINCT case when isnumeric(cast(cp.TotalComponentMass as decimal(18,5)))=isnumeric(cast(cp.TotalComponentMassSummation as decimal(18,5))) then cast(cp.TotalComponentMass as decimal(18,8)) when isnumeric(cast(cp.TotalComponentMass as decimal(18,12)))<>isnumeric(cast(cp.TotalComponentMassSummation as decimal(18,12))) then cast(cp.TotalComponentMass + '|'+ cp.TotalComponentMassSummation as varchar(600)) end
--SELECT DISTINCT '|' + CAST(ISNULL(REPLACE(acc.Name,'|','/') ,'NULL') AS NVARCHAR(50))
FROM ExtractReports.dbo.ChemicalConfilict FF
INNER JOIN Parts.ChemicalMaster Ptt WITH(NOLOCK) ON FF.ChemicalID=Ptt.ChemicalID AND Ptt.ChemicalID=FFFFF.ChemicalID
inner join parts.chemicalprofiles cp with(nolock) on cp.chemicalid=ptt.chemicalid
WHERE ff.ChemicalID=FFFFF.ChemicalID
FOR XML PATH ('')
),1,0,'')
FROM ExtractReports.dbo.ChemicalConfilict FFFFF
after run statment above i get error
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
totalcomponentmass
190976.89922
TotalComponentMassSummation
28279.27133
so how to solve this issue please ?
Expected result will be
190976.89922 | 28279.27133