CODEWITHSUNDEEP
sqlsql-server
dwieeb
dwieeb

Reputation: 218

Use column defined from subquery in query

I need to use the value obtained from SEATS_RESERVED like so. The query below gives me an Invalid column name 'SEATS_RESERVED' error.

SELECT *,
SEATS_RESERVED =
(SELECT COUNT(UID)
FROM person WHERE person.RES_DATE = reservation_dates.RES_DATE 
AND person.ARCHIVE = 'FALSE')
FROM reservation_dates
WHERE TERM = ?
AND SEATS_RESERVED < MAX_SEATS;

Upvotes: 1

Views: 463

Answers (2)

bv8z
bv8z

Reputation: 975

You're trying to set SEATS_RESERVED equal to a value in your subquery, but you haven't declared SEATS_RESERVED yet. Also, where is MAX_SEATS defined?

How about this:

DECLARE @MAX_SEATS INT
SET @MAX_SEATS = <some integer>


SELECT *,
   (SELECT COUNT(UID) FROM person WHERE person.RES_DATE = reservation_dates.RES_DATE 
AND person.ARCHIVE = 'FALSE' HAVING COUNT(UID) < @MAX_SEATS;) AS SEATS_RESERVED
FROM reservation_dates
WHERE TERM = ?

Upvotes: -1

MatBailie
MatBailie

Reputation: 86706

You can't create a derived field in the SELECT and reference it in the WHERE clause.

There are several options to deal with that, here is one with the least changes to your query.

SELECT * FROM
(
  SELECT *,
  SEATS_RESERVED =
  (SELECT COUNT(UID)
  FROM person WHERE person.RES_DATE = reservation_dates.RES_DATE 
  AND person.ARCHIVE = 'FALSE')
  FROM reservation_dates
  WHERE TERM = ?
)
  AS data
WHERE SEATS_RESERVED < MAX_SEATS;

Upvotes: 4

Related Questions