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

Question

I work with SQL Server 2012 I get this error:

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

I think this is because it can't handle 'N/A' OR Null or Text.

How to modify dynamic SQL to accept N/A AND NULL ?

How to solve this issue please ?

My sample data

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
                  where EStrat <> 'N/A'
                  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)

Learn this lesson well. You cannot debug dynamic sql if you cannot see it! So - print or select the query you generate and add that value to your question. — SMor, Nov 15 '20 at 02:59
Tip: If you copy the SQL statement that is generated to a query window in SSMS you can use the _parse_ function to validate the syntax. For more information see [SQL Server Management Studio and T-SQL Options to Prevent Code from Running in Production](https://www.mssqltips.com/sqlservertip/5835/sql-server-management-studio-and-tsql-options-to-prevent-code-from-running-in-production/). — HABO, Nov 15 '20 at 04:30

DerSchnitz · Accepted Answer · 2020-11-15T03:12:05.807

TRANSLATE was introduced in SQL Server 2017

Create a function as alternative like explained here or something like this:

USE master
CREATE FUNCTION dbo.TRANSLATE
(
    @inputString NVARCHAR(MAX),
    @characters NVARCHAR(MAX),
    @translations NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @pos INT = 0;
    DECLARE @len INT = LEN(@characters)
    DECLARE @result NVARCHAR(MAX) = @inputString
    WHILE (@pos < @len)
    BEGIN
        SET @pos += 1
        SET @result = REPLACE(@result,SUBSTRING(@characters,@pos,1),SUBSTRING(@translations,@pos,1))
    END
    RETURN @result

END

To handle the 'N/A' or NULL text expand the

translate(Name, ''VDCA'', space(4))

part with (already changed to that user-defined function)

CAST(LTRIM(RTRIM(ISNULL(master.dbo.translate(CASE WHEN Name = ''N/A'' THEN ''0'' ELSE Name END, ''VDCA'', space(4)),''0''))) AS INT)

The following doesn't look quite right either (pretty sure you can fix that on your own):

' Having Count(1)> = '

To debug your code further, change from EXEC (@SQL) to PRINT @SQL to see what's actually being executed.

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

1 Answers1