Solved

SQL error: "expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'"

Posted on 2016-11-30
6
109 Views
Last Modified: 2016-11-30
I had this question after viewing Need Distinct Value from Joined Tables in MSSQL.

With this SQL query, I am trying to match the faculty members with their topics. The user is presented with a dropdown of Research Topics, When selected, the user should be presented with a list of faculty members who are working in research on those topics.

My Query:

SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY            
JOIN (SELECT DISTINCT
suapp.RESEARCH_MATCH.Faculty_UUID
FROM suapp.RESEARCH_MATCH
JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code
WHERE suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID ORDER BY suapp.FACULTY.Last_Name, suapp.FACULTY.First_Name;

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

suapp-research-dashboard-example-deb.pdf
Thank you for any help in this situation.
Debra Turner
0
Comment
Question by:Debra Turner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 41907415
First of all, please use the embed CODE button..

The issue is a copy'n'paste error. You have simply a WHERE where none is needed. E.g.

SELECT  First_Name ,
        Last_Name ,
        Department_Name ,
        suapp.FACULTY.Faculty_UUID ,
        Faculty_Email ,
        Faculty_Title ,
        Faculty_Profile_Link
FROM    suapp.FACULTY
        JOIN ( SELECT DISTINCT
                        suapp.RESEARCH_MATCH.Faculty_UUID
               FROM     suapp.RESEARCH_MATCH
                        JOIN suapp.AREA_TOPIC.Area_Topic_Code ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
                                                                 AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
             ) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID
ORDER BY suapp.FACULTY.Last_Name ,
        suapp.FACULTY.First_Name;

Open in new window


btw, please use table alias names and qualify all used column names with it.
1
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41907418
I think the JOIN ... ON needs to have some kind of equality expression in the ON clause.  This is just a guess, but maybe something along these lines:

JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
1
 

Author Comment

by:Debra Turner
ID: 41907438
Dear Stefan,
Thank you for your quick response. I am getting new errors with these changes.

Invalid object name 'suapp.AREA_TOPIC.Area_Topic_Code'

And I'm not sure what you mean by:
"btw, please use table alias names and qualify all used column names with it"
Thank you for your help.
debra
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Debra Turner
ID: 41907452
Dear Ray,

I changed my query to reflect your example and am now getting a new error.
Error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'suapp.AREA_TOPIC.Area_Topic_Code'.

SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY            
JOIN (
SELECT DISTINCT
suapp.RESEARCH_MATCH.Faculty_UUID
FROM suapp.RESEARCH_MATCH
JOIN suapp.AREA_TOPIC.Area_Topic_Code
ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
WHERE suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
AND suapp.AREA_TOPIC.Area_Topic_Code = 'CD-a'
) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID ORDER BY suapp.FACULTY.Last_Name, suapp.FACULTY.First_Name;

With your suggestion, the above is my new query that is getting the error (inside the sql console)
Thank you for your help.
debra
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41907462
Yup, there is another problem. You "joined" a column instead of a table.

This should do it:
SELECT  F.First_Name ,
        F.Last_Name ,
        F.Department_Name ,
        F.Faculty_UUID ,
        F.Faculty_Email ,
        F.Faculty_Title ,
        F.Faculty_Profile_Link
FROM    suapp.FACULTY F
        INNER JOIN ( SELECT DISTINCT
                            RMI.Faculty_UUID
                     FROM   suapp.RESEARCH_MATCH RMI
                            INNER JOIN suapp.AREA_TOPIC [AT] ON RMI.Area_Topic_Code = [AT].Area_Topic_Code
                                                                AND [AT].Area_Topic_Code = 'CD-a'
                   ) RM ON F.Faculty_UUID = RM.Faculty_UUID
ORDER BY F.Last_Name ,
        F.First_Name;

Open in new window


btw, an EXIST is in this case sometimes better (no DISTINCT necessary):
SELECT  F.First_Name ,
        F.Last_Name ,
        F.Department_Name ,
        F.Faculty_UUID ,
        F.Faculty_Email ,
        F.Faculty_Title ,
        F.Faculty_Profile_Link
FROM    suapp.FACULTY F
WHERE   EXISTS ( SELECT *
                 FROM   suapp.RESEARCH_MATCH RMI
                        INNER JOIN suapp.AREA_TOPIC [AT] ON RMI.Area_Topic_Code = [AT].Area_Topic_Code
                                                            AND [AT].Area_Topic_Code = 'CD-a'
                                                            AND F.Faculty_UUID = RM.Faculty_UUID )
ORDER BY F.Last_Name ,
        F.First_Name;

Open in new window

1
 

Author Closing Comment

by:Debra Turner
ID: 41907480
Awesome. Thank you for the help!
Debra
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question