Click here to Skip to main content
14,734,606 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I work on SQL server 2012 I Face issue as below :

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1.2kV' to data type int.

this error done where converting Name to number but it is failed .

as Example Name have value 1.2v then if i get numbers from name so will be 1.2 and on this case will not get error

and if i found N/A convert to 0

so How to get Numbers from Name to prevent it from display this error .

Query Generated :

SELECT PartID, Code, Count(1) as ConCount
FROM #PartAttributes PM
INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey Where 1=1 and ( (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4))  >1000) OR (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4)) >280) OR (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4)) = 'N/A') ) Group By PartID,Code  Having Count(1)> = 4


issue on query above on Name on Where condition .

What I have tried:

if object_id(N'tempdb..#PartAttributes') is not null drop table #PartAttributes
    
    
 if object_id(N'tempdb..#Condition') is not null drop table #Condition
    
    
 if object_id(N'tempdb..#Allfeatures') is not null drop table #Allfeatures
 if object_id(N'tempdb..#Codes') is not null drop table #Codes
    
 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',' '),
    ('8535400000','1505730036',NULL,'>280AV',' '),
    ('8535400000','1505730036',NULL,'N/A',' '),
    ('8535400000','1505730036',NULL,NULL,' ')
                            
    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,'1.2kV'),
    (4977936,1505730036,280,'280VDC'),
    (4977935,1505730036,280,'100V'),
    (4977808,1505730036,280,'N/A'),
    (4979054,1505730036,280,'24VAC/DC')
                            
     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 replace(Name, ''VDC'', space(4)) '), 
    CASE 
    WHEN EStrat = 'N/A' THEN '= ''N/A''' 
    ELSE CAST(LEFT(SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500), PATINDEX('%[^<>0-9.-]%', SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500) + 'X') -1) AS nvarchar(2500)) 
    END, ')')
       FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue = 0
      WHERE EStrat IS NOT NULL
    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)
Posted
Updated 16-Nov-20 23:24pm

1 solution

The string "1.2kV" is not a valid number. You need to parse the "kV" out and just paas the "1.2" to the convert call.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900