I work on SQL server 2012 I Face issue display for me as
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '280VDC' to data type int.
How to solve this issue please
my script as below
create table #Allfeatures
(
ZPLID INT,
ZFeatureKey nvarchar(20),
IsNumericValue int
)
insert into #Allfeatures(ZPLID,ZFeatureKey,IsNumericValue)
values
(75533,'1505730036',0)
create table #Condition
(
Code nvarchar(20),
ZFeatureKey nvarchar(20),
ZfeatureType nvarchar(20),
EStrat nvarchar(20),
EEnd NVARCHAR(10)
)
insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)
values
('8535400000','1505730036',NULL,'>1000',' ')
CREATE TABLE #PartAttributes
(
PartID INT,
ZFeaturekEY NVARCHAR(20),
AcceptedValuesOption_Value INT,
Name nvarchar(20)
)
insert into #PartAttributes(PartID,ZFeaturekEY,AcceptedValuesOption_Value,Name)
values
(4977941,1505730036,280,'280VDC'),
(4977936,1505730036,280,'280VDC'),
(4977935,1505730036,280,'280VDC')
DECLARE @Sql nvarchar(max)
DECLARE @ConStr nvarchar(max)
SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName
FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
SET @Sql= CONCAT(' SELECT PartID,Code,Count(1) as ConCount
FROM
#PartAttributes PM
INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',
'Where (1=1 and ',@ConStr,' ) Group By PartID,Code ' ,
' Having Count(1)>= ',(SELECT COUNT(1) FROM #Condition))
EXEC (@SQL)