error An expression of non-boolean type specified in a context where a condition is expected, near 'MainFeature'.

Question

I work on sql server 2012 when run statement below i get error

so i need to solve all issues exist on it until statement run

An expression of non-boolean type specified in a context where a condition is expected, near 'MainFeature'.

so please can you help me

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)

           ----select * from #Condition                 
    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,'MainFeature',' '),
    ('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)
    values
    (4977941,1505730036,1.2),
    (4977936,1505730036,280),
    (4977935,1505730036,100),
    (4979054,1505730036,24),
    (4979043,1505730036,30)

     DECLARE @Sql nvarchar(max)
     DECLARE @ConStr nvarchar(max)

 SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')   
                FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey 
                FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')


    ---EXE(@ConStr)
       -- print @ConStr 
       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))

 --print @SQL
    EXEC (@SQL)

so how to solve issues until query run

Answer 1

Hi @ahmed salah ,

if you face this kind of issue, you could consider different conditions when you would like to concatenate one string.

What is the expected output of @ConStr if EStrat is 'N/A' or NULL?

Please refer below example:

DECLARE @Sql nvarchar(max)  
DECLARE @ConStr nvarchar(max)  
                                                 
SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And '+  
case when PATINDEX('%[><=]%', EStrat)>0 then   
 'AcceptedValuesOption_Value '   
 when EStrat is null then  
 'cast(AcceptedValuesOption_Value as varchar(100)) '  
 else   
 'cast(AcceptedValuesOption_Value as varchar(100))='  
 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  
, IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')     
            FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey   
            FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')  
       
---EXE(@ConStr)  
--print @ConStr   
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))  
      
--print @SQL  
EXEC (@SQL)  

If you have any other issue, please provide more sample data and expected output.

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.