How to solve error Conversion failed when converting the nvarchar value '24VAC/D

Transact-SQL
, ,
1

I work on SQL server 2014 I get error when run statement below

error say

Conversion failed when converting the nvarchar value '24VAC/DC' to data type int.

I got error when execut dynamic sql

EXEC (@SQL)

so how to solve this error please

data sample

IF OBJECT_ID('dbo.TAllfeatures') IS NOT NULL
 DROP TABLE dbo.TAllfeatures
 IF OBJECT_ID('dbo.TCondition') IS NOT NULL
 DROP TABLE dbo.TCondition
 IF OBJECT_ID('dbo.TPartAttributes') IS NOT NULL
 DROP TABLE dbo.TPartAttributes
 IF OBJECT_ID('dbo.TAllData') IS NOT NULL
 DROP TABLE dbo.TAllData
    
 CREATE TABLE [dbo].[TAllfeatures](
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [FeatType] [int] NULL,
     [AcceptedValueID] [int] NULL,
     [IsNumericValues] [int] NULL
 ) ON [PRIMARY]
    
 GO
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, NULL, 0, 0, 0)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730001, 2044, 155, 0)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730011, 2044, 274, 1)
 INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730036, 2044, 271, 0)
    
    
 CREATE TABLE [dbo].[TCondition](
     [TradeCodeControlID] [int]  NOT NULL,
     [VersionYear] [int] NULL,
     [Version] [float] NULL,
     [CodeTypeID] [int] NULL,
     [RevisionID] [bigint] NULL,
     [Code] [varchar](20) NULL,
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
     [ZfeatureType] [nvarchar](200) NULL,
     [EStrat] [nvarchar](2500) NULL,
     [EEnd] [nvarchar](2500) NULL
 ) ON [PRIMARY]
    
    
    
    
 INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8123, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730001, NULL, N'In(''Surge Protector'',''Surge Protector for Media Lines Only'',''Outlet Strip, Surge Protector'',''PDU, Surge Protector'',''Surge Lightning Arrester'',''Surge Arrester'',''Surge Protection Module'',''Lightning Arrester'',''Lightning Current Arrester'',''Protection Device'',''Surge Voltage Equipment'',''Isolated'',''Surge Protection'',''Coaxial'',''Base Element'')', N'')
 INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, NULL, N'>1000', N'')
    
    
    
 CREATE TABLE [dbo].[TPartAttributes](
     [PartID] [int] NOT NULL,
     [ZfeatureKey] [bigint] NULL,
     [AcceptedValuesOption_Value] [float] NULL,
     [Name] [nvarchar](500) NOT NULL
 ) ON [PRIMARY]
    
 GO
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730036, 24, N'24VAC/DC')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730001, NULL, N'Surge Voltage Equipment')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730036, 5, N'5V')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730001, NULL, N'Attachment Plug')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730001, NULL, N'Surge Protection Module')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730036, 480, N'480V')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730001, NULL, N'Surge Protector')
 INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730036, NULL, N'120V, 240V')
    
 CREATE TABLE dbo.TAllData
         (
    
         PartID INT,    
         Code VARCHAR(20),    
         CodeTypeID INT,    
         RevisionID BIGINT,    
         ZPLID INT,    
         ConCount INT
    
         )
         SET ANSI_PADDING ON
 SET ANSI_WARNINGS ON
            
 DECLARE @ConStr nvarchar(max)=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')')   --ValueName
                 FROM dbo.TCondition CC  INNER JOIN dbo.TAllfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
                
    
                    
                    
    
                    
                 DECLARE @Sql nvarchar(max)= CONCAT('INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
                    
                 FROM 
                 dbo.TPartAttributes PM with(nolock) 
                 INNER JOIN    dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',                
                 'Where (1=1 and  ' ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                 ' Having Count(1)>= ',(SELECT COUNT(1) FROM TCondition with(nolock)))
    
            
            --print @SQL
             EXEC (@SQL)
2

did you look at what you are producing in the @SQL Statement? Pretty sure this has something to do with it.

AND NAME > 1000

3

yes i know
i need to know how to change query above
to not get this error

4

what i try

 DECLARE @ConStr nvarchar(max)=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ''', CC.ZfeatureKey , '''', IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , LEFT(EStrat, 1), '''', SUBSTRING(EStrat, 2, LEN(EStrat) -1), ''')')   --ValueName
                       FROM dbo.TCondition  CC INNER JOIN dbo.TAllfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
                       FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

but issue still exist

5

The > 1000 is coming out of estrat in the table TCondition. You need to change it to make it a string.

update TCondition set estrat = '>cast(1000 as char(4))'|
where TradeCodeControlID = 8124|

6

What did you expect? Values of Name

24VAC/DC
Surge Voltage Equipment
5V
Surge Protection Module
480V
Surge Protector
120V, 240V

but your query is

AND NAME > 1000
7

expected result as Name >'1000'
will be as below :

PartID Code CodeTypeID RevisionID ZPLID ConCount
1128078 8535400000 849774 307683692 75533 2
27912821 8535400000 849774 307683692 75533 2
32817870 8535400000 849774 307683692 75533 2
8

What does that mean and what does that do?