Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);

HAVING (SELECT COUNT(*) FROM
(
    SELECT *
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
    AND da2.RoomTypeId = DA.RoomTypeId
    AND da2.Date >= @FromDate AND da2.Date < @ToDate
    AND da2.IsSold = 0
    AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)) T) = @StayDates
    AND ( (@AllowOnRequestBookings = 'False' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)  
      >= 0 ) )
      AND ( (@AllowOnRequestBookings = 'True' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) 
      > 0 )
)

The last 2 AND's give me the following error messages:

Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier "da2.ContractId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier "da2.RoomTypeId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier "da2.ContractId" could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier "da2.RoomTypeId" could not be bound.

Any ideas?

share|improve this question
add comment

2 Answers 2

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    ( 
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND 
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.

share|improve this answer
add comment

You have declared the alias da2 inside a subquery,
then you referenced it outside of that subquery, where it is not recognized.

if you need "The last 2 AND's" as a filter of table T (the records that will be counted),
then try using this instead:

HAVING 
    (SELECT COUNT(*)
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
        AND da2.RoomTypeId = DA.RoomTypeId
        AND da2.Date >= @FromDate AND da2.Date < @ToDate
        AND da2.IsSold = 0
        AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date
        AND ( @AllowOnRequestBookings = 'False' OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0 )
        AND ( @AllowOnRequestBookings = 'True' OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0 )
    ) = @StayDates

Please make sure that it is the data you are expecting,
and let me know if it is something else that you need.

רועי גביש

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.