Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to reference embedded select in WHERE clause

Author  Topic 

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-22 : 10:52:18
Hi guys,

I need to check the value of a column being pulled from an embedded select in the WHERE clause of a script but can't figure out how to do it. The script looks like this:

SELECT
LIST_ID,
NAME,
ADDRESS
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST

WHERE ANSWER = 'No'

The syntax above works in Oracle but in SQL Server I receive the following error: "Invalid column name 'OHIP'."

Please help!

Thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 11:38:49
if you add a comma after [ADDRESS] then there is nothing wrong with your syntax.

The error seems to be from something other than the code you posted. Perhaps the view [VIEW_LIST] has the problem. What happens if you:
select * from VIEW_LIST
?

Be One with the Optimizer
TG
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-22 : 11:53:54
Yes, sorry, I forgot the comma in the ADDRESS but that is irrelevant. The View VIEW_LIST is fine and yes, I can SELECT * FROM VIEW_LIST.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-22 : 11:59:46
quote:
Originally posted by sqlslick

Yes, sorry, I forgot the comma in the ADDRESS but that is irrelevant. The View VIEW_LIST is fine and yes, I can SELECT * FROM VIEW_LIST.


Then please post the exact code that is generating that error. Because the object mentioned in the error is not in your code anywhere.

EDIT:
Is this a view too?
See if this returns ok:
SELECT ANSWER FROM VALID_ANSWER

Be One with the Optimizer
TG
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-22 : 13:09:55
Sorry, I was trying to simplify the example and made a typo. Query is below:

SELECT
LIST_ID,
NAME,
ADDRESS,
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST

WHERE ANSWER = 'No'

Generates error: "Invalid column name 'ANSWER'."
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 13:25:41
T-SQL does not let you use use an alias you define in the select list anywhere else in the rest of the select statement except in ORDER BY clause. So perhaps one of these?
SELECT
LIST_ID,
NAME,
ADDRESS,
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST

WHERE (SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) = 'No'


Or
SELECT * FROM (SELECT
LIST_ID,
NAME,
ADDRESS,
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST
)as s

WHERE ANSWER = 'No'
Alternatively, you could rewrite the query to join the VALID_ANSWER and VIEW_LIST in the from clause and then apply the where clause
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-22 : 15:11:00
Yes, I ended up doing a LEFT OUTER JOIN as most of the values are NULL on one side. Thanks for the tip James, but now I have another issue to deal with. I will post a separate topic.
Go to Top of Page
   

- Advertisement -