How to add 'N/A' to where condition ?

Question

I work on SQL server 2012 I face issue I need to add 'N/A' to condition as below :

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 Name >1000) Or (PM.ZfeatureKey= 1505730036 And Name >280) Or (PM.ZfeatureKey= 1505730036 And Name ='N/A') ) Group By PartID,Code  Having Count(1)> = 4

script below when executed it give me error

an expression of non boolean type specified on context on where condition is expected near ' )'

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,'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 translate(Name, ''VDCA'', space(4)) ' ) , CAST(

                           cast(LEFT(SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500),
             PATINDEX('%[^<>0-9.-]%', SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500) + 'X') -1) as nvarchar(2500))
                  --EStrat 



                  AS NVARCHAR(2500))


                  ,')')   

                  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)

Answer 1

Always print out your dynamic SQL to troubleshoot. That code generates the query:

   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 translate(Name, 'VDCA', space(4)) >1000) 
       Or (PM.ZfeatureKey= 1505730036 And translate(Name, 'VDCA', space(4)) >280) 
       Or (PM.ZfeatureKey= 1505730036 And translate(Name, 'VDCA', space(4)) ) 
       Or (PM.ZfeatureKey= 1505730036 And translate(Name, 'VDCA', space(4)) ) ) 
     Group By PartID,Code  Having Count(1)> = 4

which is missing a boolean comparison for the translate at

  Or (PM.ZfeatureKey= 1505730036 And translate(Name, 'VDCA', space(4)) ) 

Answer 2

so How to solve issue please

Answer 3

missing is 'N/A'
how to handle it

Answer 4

SET @ConStr = STUFF((
 SELECT CONCAT(' OR (PM.ZfeatureKey = ', CC.ZfeatureKey, IIF(CC.ZfeatureType = 'Qualifications', ' And AcceptedValuesOption_Value ', ' And translate(Name, ''VDCA'', space(4)) '), 
 CASE 
 WHEN EStrat = 'N/A' THEN '= ''N/A''' 
 WHEN EStrat IS NULL THEN 'IS NULL '
 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
 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'), 1, 3, '')

Answer 5

Hi @ahmed salah ,

As mentioned by other experts, TRANSLATE is applied to SQL Server 2017 and later while your SQL is SQL Server 2012. You could use REPLACE instead.

Please refer below and check whether it is helpful:

   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)  

The output of @alenzi is as below:

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  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November