I working on sql server 2012 i face error
conversion failed when converting varchar value 'Manual reset' to data type int
error done on script below
to solve issue
instead of
AcceptedValuesOption_Value ='Manual Reset'
use
Name ='Manual Reset'
so How to modify statment below to accept name instead of AcceptedValuesOption_Value
I use AcceptedValuesOption_Value for numbers only
and Name for text
create table dbo.condition
(
TradeCodeControlID int,
VersionYear int,
[Version] int,
CodeTypeID int,
RevisionID int,
Code nvarchar(200),
ZPLID int,
ZfeatureKey bigint,
ZfeatureType int,
EStrat nvarchar(100),
EEnd nvarchar(20)
)
insert into dbo.condition(TradeCodeControlID,VersionYear,[Version],CodeTypeID,RevisionID,Code,ZPLID,ZfeatureKey,ZfeatureType,EStrat,EEnd)
values
(8122,2020,26,849774,307683692,8536509065,363712,1509900011,NULL,N'=''Manual Reset''',NULL)
--update dbo.condition set eend=''
CREATE TABLE [dbo].[PartAttributes](
[PartID] [int] NOT NULL,
[ZfeatureKey] [bigint] NULL,
[AcceptedValuesOption_Value] int NULL,
[Name] [nvarchar](500) NOT NULL
) ON [PRIMARY]
insert into [dbo].[PartAttributes](PartID,ZfeatureKey,AcceptedValuesOption_Value,Name)
values
(7368955,1509900011,NULL,'Manual Reset'),
(7368956,1509900011,NULL,'Manual Reset'),
(7368957,1509900011,NULL,'Manual Reset'),
(7368958,1509900011,NULL,'Manual Reset'),
(7368959,1509900011,NULL,'Manual Reset'),
(3733735,1509900011,80,'80°C'),
(3733736,1509900011,80,'80°C'),
(3733737,1509900011,80,'80°C'),
(3733738,1509900011,80,'80°C'),
(3733739,1509900011,80,'80°C')
CREATE TABLE [dbo].[Allfeatures](
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[FeatType] [int] NULL,
[AcceptedValueID] [int] NULL,
[IsNumericValues] [int] NULL
) ON [PRIMARY]
insert into [dbo].[Allfeatures](ZPLID,ZfeatureKey,FeatType,AcceptedValueID,IsNumericValues)
values
(363712,NULL,0,0,0),
(363712,1509900011,2044,2370,1)
CREATE TABLE dbo.AllData
(
PartID INT,
Code VARCHAR(20),
CodeTypeID INT,
RevisionID BIGINT,
ZPLID INT,
ConCount INT
)
DECLARE @Sql nvarchar(max)
DECLARE @Con nvarchar(max)
DECLARE @ConStr nvarchar(max)
SET @Con= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')
FROM dbo.Condition CC INNER JOIN dbo.Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' + case when PATINDEX('%[><=]%', EStrat)>0 then
'AcceptedValuesOption_Value '
when EStrat is null then
'Name '
else
'Name '
end ) , case when PATINDEX('%[><=]%', EStrat)>0 then
CAST(EStrat AS NVARCHAR(2500))
when EStrat is null then
' is null '
else
''+CAST(EStrat AS NVARCHAR(2500)) +''
end ,')') --ValueName
FROM dbo.condition CC INNER JOIN [dbo].[Allfeatures] AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr )
SET @Sql= CONCAT('INSERT INTO dbo.AllData',' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
FROM
dbo.PartAttributes PM
INNER JOIN dbo.Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',
'Where (1=1 and ',@Con , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
' Having Count(1)>= ',(SELECT COUNT(1) FROM dbo.Condition))
-- print @SQL
EXEC (@SQL)
expected result
| PartID | Code | CodeTypeID | RevisionID | ZPLID | ConCount |
|---|---|---|---|---|---|
| 7368955 | 8536509065 | 849774 | 307683692 | 363712 | 1 |
| 7368956 | 8536509065 | 849774 | 307683692 | 363712 | 1 |
| 7368957 | 8536509065 | 849774 | 307683692 | 363712 | 1 |
| 7368958 | 8536509065 | 849774 | 307683692 | 363712 | 1 |
| 7368959 | 8536509065 | 849774 | 307683692 | 363712 | 1 |