Ошибка an expression of non boolean type - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Getting this error with the following query in SQL Server 2012.

An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’.

CREATE FUNCTION [dbo].[GetPMResources](@UserResourceNo nvarchar(250))

   RETURNS @Resources TABLE (
   ResourceNo nvarchar(250) COLLATE Latin1_General_CS_AS not null,
   Name nvarchar(250) COLLATE Latin1_General_CS_AS not null
   ) 
  AS
  BEGIN

        Declare @RoleID int, @UserDivision nvarchar(20)
        SELECT TOP(1) @RoleID = r.ReportingRole, @UserDivision = r.DivisionCode
        FROM Resource r 
        WHERE r.ResourceNo = @UserResourceNo



        INSERT @Resources
        SELECT r.ResourceNo,Name = r.ResourceNo + ' ' + r.Name
        FROM Resource r WHERE r.ResourceNo IN
                        ( 
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN Job j ON j.JobNo = m.JobNo
                            WHERE j.ProjectManagerNo = @UserResourceNo 
                            OR
                            j.AlternateProjectManagerNo = @UserResourceNo
                        ) 
                        OR
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )                   
        RETURN 
  END

asked May 29, 2014 at 8:06

Sohail xIN3NSohail xIN3N

2,9312 gold badges30 silver badges29 bronze badges

An expression of non-boolean type specified in a context where a condition is expected

I also got this error when I forgot to add ON condition when specifying my join clause.

answered Apr 19, 2016 at 16:46

Lori KentLori Kent

3092 silver badges4 bronze badges

That is invalid syntax. You are mixing relational expressions with scalar operators (OR). Specifically you cannot combine expr IN (select ...) OR (select ...). You probably want expr IN (select ...) OR expr IN (select ...). Using union would also work: expr IN (select... UNION select...)

answered May 29, 2014 at 8:10

Remus RusanuRemus Rusanu

287k40 gold badges437 silver badges567 bronze badges

Your problem might be here:

OR
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )

try changing to

OR r.ResourceNo IN
                        (
                            SELECT m.ResourceNo FROM JobMember m
                            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
                            WHERE t.TaskManagerNo = @UserResourceNo
                            OR
                            t.AlternateTaskManagerNo = @UserResourceNo
                        )

answered May 29, 2014 at 8:12

Rahul GargRahul Garg

2781 silver badge6 bronze badges

You can also rewrite it like this

FROM Resource r WHERE r.ResourceNo IN
        ( 
            SELECT m.ResourceNo FROM JobMember m
            JOIN Job j ON j.JobNo = m.JobNo
            WHERE j.ProjectManagerNo = @UserResourceNo 
            OR
            j.AlternateProjectManagerNo = @UserResourceNo

            Union All

            SELECT m.ResourceNo FROM JobMember m
            JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo
            WHERE t.TaskManagerNo = @UserResourceNo
            OR
            t.AlternateTaskManagerNo = @UserResourceNo
                
        )

Also a return table is expected in your RETURN statement

SwissCodeMen

4,1056 gold badges23 silver badges33 bronze badges

answered May 29, 2014 at 8:15

JadeJade

2,9621 gold badge11 silver badges9 bronze badges

In my case, i got the same error, but it was because a minus sign was a hyphen. Maybe this can help someone.

answered Mar 1 at 12:48

César LeónCésar León

2,9111 gold badge21 silver badges18 bronze badges

Источник

sqlfiddle.com/#!6/14e507/16

Формулировка:
«Нужно удалить лишние записи и оставить только клиентов заведенных первыми»
Номер паспорта должен быть уникальный.
Ошибка: An expression of non-boolean type specified in a context where a condition is expected, near ‘)’.

Собственно, попробовал в SQLLite, и все нормально отработало:
sqlfiddle.com/#!7/68e68/2

Вопрос задан

более трёх лет назад
4350 просмотров

Пригласить эксперта

DELETE e
FROM #employees e
INNER JOIN (
	SELECT id
		,RANK() OVER (PARTITION BY pass_num ORDER BY ID) RNK
	FROM #employees
	) X ON X.id = e.id
WHERE x.RNK > 1

С MSSQL практически не знаком, но ошибка вполне понятная.
В HAVING ожидается выражение, результат которого — TRUE или FALSE. Вы же пытаетесь подсунуть целочисленное значение MIN(id).

Показать ещё
Загружается…

09 июн. 2023, в 22:45

1000 руб./за проект

09 июн. 2023, в 22:39

500 руб./в час

09 июн. 2023, в 22:19

8000 руб./за проект

Минуточку внимания

Источник

In this guide, you will learn about the error «Expression of Non-Boolean Type Specified in a Context Where a Condition is Expected» and how to resolve it. This error usually occurs when using SQL Server and writing T-SQL queries. Follow along for a step-by-step solution to fix this error.

What Does the Error Mean?
Common Scenarios
Step-by-Step Solution
FAQ

What Does the Error Mean?

In SQL Server, when writing a T-SQL query, you may encounter the error message «Expression of Non-Boolean Type Specified in a Context Where a Condition is Expected.» This error occurs when an expression in a conditional statement returns a non-boolean value (i.e., not TRUE or FALSE), while the SQL Server expects a boolean value.

For example, consider the following query:

SELECT * FROM Employees WHERE Age;

Here, the WHERE clause expects a boolean condition to filter the results, but the Age column returns an integer value. This will result in the mentioned error.

Common Scenarios

Here are some common scenarios where this error might occur:

Using a column name directly in a WHERE clause without any comparison operator.
Using a non-boolean function as a condition in a WHERE or HAVING clause.
Using a non-boolean expression in a CASE statement.

Step-by-Step Solution

Follow these steps to resolve the error:

Identify the non-boolean expression in the query.
Update the non-boolean expression to return a boolean value.
Re-run the query to verify that the error is resolved.

Here’s an example to illustrate the solution:

Identify the non-boolean expression:

SELECT * FROM Employees WHERE Age;

In this query, the Age column is used directly in the WHERE clause, which is a non-boolean expression.

Update the non-boolean expression to return a boolean value:

SELECT * FROM Employees WHERE Age > 30;

Now, the WHERE clause contains a boolean expression (Age > 30), which returns either TRUE or FALSE.

Re-run the query to verify that the error is resolved. The updated query will now execute without throwing the error.

FAQ

1. What is a boolean expression in SQL?

A boolean expression in SQL is an expression that evaluates to either TRUE or FALSE. Boolean expressions are commonly used in conditional statements like WHERE and HAVING clauses. Examples of boolean expressions include comparisons between column values and constants, like Age > 30 or Salary <= 50000.

2. Can I use aggregate functions in a `WHERE` clause?

No, aggregate functions like SUM, COUNT, AVG, etc., cannot be used directly in a WHERE clause. Instead, you should use them in a HAVING clause after the GROUP BY clause to filter the results based on aggregated values.

3. How can I use a non-boolean function in a conditional statement?

To use a non-boolean function in a conditional statement, you need to convert the function’s output into a boolean expression by using comparison operators like =, <>, <, >, <=, or >=. For example, if you want to filter rows based on the length of a string column, you can use the LEN function with a comparison operator:

SELECT * FROM Employees WHERE LEN(FirstName) > 5;

4. Can I use a subquery in a `WHERE` clause?

Yes, you can use a subquery in a WHERE clause, but the subquery must return a single value or a list of values. You can use comparison operators like IN, EXISTS, and ANY to compare the column values with the subquery results. For example:

SELECT * FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

5. Can I use a `CASE` statement in a `WHERE` clause?

Yes, you can use a CASE statement in a WHERE clause, but you need to ensure that the CASE statement returns a boolean value. For example:

SELECT * FROM Employees WHERE (CASE WHEN Age > 30 THEN 1 ELSE 0 END) = 1;

In this query, the CASE statement returns either 1 or 0 based on the Age column value, and the WHERE clause compares the result with 1 to filter the rows.

SQL Server Data Types
SQL Server Comparison Operators
SQL Server CASE statement

Источник

The problem is on this line:

IF @StartDate AND @EndDate IS NOT NULL

You’re expecting this to be evaluated as:

IF (@StartDate AND @EndDate) IS NOT NULL

But the way SQL Server evaluates it is:

IF (@StartDate) AND (@EndDate IS NOT NULL)

So the error is that @StartDate is not a boolean expression, and cannot be used as part of the IF expression.

Instead, you’ll need to do something like:

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL

Источник

Question
So I have a query that is generated by another program I wrote. Occasionally I’ll get the error from the subject line, but not always. Here’s an example of one of the queries that bounces the error:
SELECT DISTINCT MP.SecType AS SSSecType, MP.Symbol AS SSSymbol, MSM.FullName AS SSFullName, MPri.ShareValue AS SSShareValue, MPri.Price AS SSPrice, UPPER(MSM.StateID) AS SSStateID, MSM.AxysSecUserDef2ID AS SSRating, CONVERT(varchar(10), MBI.CDate, 101) AS SSCallDate, MBI.CPrice AS SSCallPrice, MBI.YTMonMarket AS SSYieldMat, MBI.YieldToWorst AS SSYieldWorst, MBI.DurationToWorst AS SSDurWorst, CONVERT(varchar(10), MSM.MatureDate, 101) AS SSMatDate FROM MoxyPosition AS MP LEFT JOIN MoxySecMaster AS MSM ON MP.Symbol = MSM.Symbol AND MP.SecType = MSM.SecType LEFT JOIN MoxyPrice AS MPri ON MP.Symbol = MPri.Symbol AND MP.SecType = MPri.SecType LEFT JOIN MoxyBondInfo AS MBI ON MP.Symbol = MBI.Symbol AND MP.SecType = MBI.SecType WHERE MSM.MatureDate <> '01/02/1900';

It’s worth noting that if I copy this exact code into MS SQL Server Management Studio it runs like a champ. What’s causing this?
*** Edit *** — I just noticed that I only get the error if the WHERE clause includes an operator that includes the ‘<‘ character (eg ‘<>’, ‘<‘, ‘<=’).
- Edited by
  
  Tuesday, February 2, 2010 9:55 PM
  New information added.

Answers

It wasn’t my ActionScript! It was the VB! I added these statements to my functions in the .asmx file and all is well!

WhereClause = WhereClause.replace(«<», «<«)

OrderByClause = OrderByClause.replace(«<», «<«)

Thanks again guys, couldn’t have gotten this far without your help!
- Marked as answer by
  RFrost
  Tuesday, February 9, 2010 4:59 PM

Источник

Минуточку внимания

Table of Contents

What Does the Error Mean?

Common Scenarios

Step-by-Step Solution

FAQ

1. What is a boolean expression in SQL?

2. Can I use aggregate functions in a WHERE clause?

3. How can I use a non-boolean function in a conditional statement?

4. Can I use a subquery in a WHERE clause?

5. Can I use a CASE statement in a WHERE clause?

Question

Answers

2. Can I use aggregate functions in a `WHERE` clause?

4. Can I use a subquery in a `WHERE` clause?

5. Can I use a `CASE` statement in a `WHERE` clause?