Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Solved
SQL error: "expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'"
Posted on 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:
The error:
suapp-research-dashboard-example-deb.pdf
Thank you for any help in this situation.
Debra Turner
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_Topi c_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
[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
Active today
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.
btw, please use table alias names and qualify all used column names with it.
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;
btw, please use table alias names and qualify all used column names with it.
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_Topi c_Code
Dear Stefan,
Thank you for your quick response. I am getting new errors with these changes.
Invalid object name 'suapp.AREA_TOPIC.Area_Top
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
Thank you for your quick response. I am getting new errors with these changes.
Invalid object name 'suapp.AREA_TOPIC.Area_Top
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
Dear Ray,
I changed my query to reflect your example and am now getting a new error.
Error
With your suggestion, the above is my new query that is getting the error (inside the sql console)
Thank you for your help.
debra
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_Topi c_Code
WHERE suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topi c_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
Active today
Yup, there is another problem. You "joined" a column instead of a table.
This should do it:
btw, an EXIST is in this case sometimes better (no DISTINCT necessary):
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;
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;
Featured Post
Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
- Superb Internet
- IT Administration
- Ransomware
- Storage Software
- Databases
- Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
Suggested Courses
Course of the Month7 days, 12 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.