Use column defined from subquery in query

Question

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;

if SEATS_RESERVED is a variable declare it with@. for example: @SEATS_RESERVED — Scorpion
– Scorpion, Commented Feb 14, 2012 at 15:10

MatBailie · Accepted Answer · 2012-02-14 15:12:15Z

4

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;

answered Feb 14, 2012 at 15:12

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bv8z · Accepted Answer · 2012-02-14 15:14:48Z

-1

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 = ?

answered Feb 14, 2012 at 15:14

bv8z

9753 gold badges9 silver badges19 bronze badges

1 Comment

MatBailie Over a year ago

It's not a variable. It's an alternative syntaxt to SELECT *, (sub-query) AS SEATS_RESERVED FROM .... And is appears that MAX_SEATS is a field in the reservation_dates table.

Collectives™ on Stack Overflow

Use column defined from subquery in query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related