[RESOLVED] SQL Server 2005 Error On Create View

[GaryMazzone]

I'm having an problem with this SQL:

IF NOT EXISTS(SELECT [name] FROM SYS.VIEWS WHERE [name] = 'Vw_RoomBlock_ReportingRoomTotals_1' AND schema_id = (SELECT schema_id FROM SYS.SCHEMAS WHERE [name] = 'Reporting'))
BEGIN
    Create  VIEW [Reporting].[Vw_RoomBlock_ReportingRoomTotals_1] 
    AS 
 
    Select 
        distinct(RoomBlock.RoomBlockId), 
        EntityType.Name as EntityType,
        RoomBlock.EntityId,
        RoomBlock.EntityTypeId, 
        RoomBlock.StartDate, 
        temp.RoomRevenue as RoomRevenue,
        RoomBlock.TotalRooms as CalculatedRoomTotal,
        RoomBlock.AlternateStartDate,
        RoomBlock.IsAlternateStartDateSelected,
        RoomBlock.TotalRooms,
        RoomBlock.IsSenderCopy,
        BusinessDataInfo.BusinessDataId,
        BusinessDataInfo.BusinessDataTypeId,
        BusinessDataType.Name as BusinessDataType,
        RoomBlock.Budget as RoomBlockBudget
    from  
        RoomBlock with (NOLOCK)
    left join RoomRequirement with (NOLOCK)
    on RoomBlock.RoomBlockId=RoomRequirement.RoomBlockId
    left join RoomAvailability with (NOLOCK)
    on RoomBlock.RoomBlockId=RoomAvailability.RoomBlockId
    left Join BusinessDataInfo with (NOLOCK)
    on RoomBlock.EntityId=BusinessDataInfo.BusinessDataInfoId
    and RoomBlock.EntityTypeId=14
    left join BusinessDataType with (NOLOCK)
    on BusinessDataInfo.BusinessDataTypeId=BusinessDataType.BusinessDataTypeId
    left join EntityType with (NOLOCK)
    on EntityType.EntityTypeId=RoomBlock.EntityTypeId
    left outer join 
    (SELECT RoomBlockId,sum(isnull(RoomAvailability.SingleQuantity,0)*isnull(RoomAvailability.SingleRate,0) 
    + isnull(RoomAvailability.DoubleQuantity,0)*isnull(RoomAvailability.DoubleRate,0) 
    + isnull(RoomAvailability.TripleQuantity,0)*isnull(RoomAvailability.TripleRate,0) 
    + isnull(RoomAvailability.QuadQuantity,0) * isnull(RoomAvailability.QuadRate,0)) as 'RoomRevenue'
    from RoomAvailability group by RoomBlockId) temp
    on temp.roomblockid = RoomBlock.RoomBlockId
 
    GO
    GRANT SELECT ON Reporting.Vw_RoomBlock_ReportingRoomTotals_1 TO PUBLIC
    GO
END

The error is:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'VIEW'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near 'RoomBlockId'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.

If I run the create view outside the If BLock it works.

Thanks
Gary