March 16, 2011 at 7:14 am
This I don't understand at all. I am getting the 207 message inside the Case Statement. When I mouse over the fields I get 'Invalid column name xxxxx' yet within the select statement everything is fine. Once I work myslef through the code I will be converting this to a stored procedure if this makes any difference.
Any Ideas?
thanks in advance
Mike
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
USE [SPIResTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
-- Local Variables
declare @Dayin int = 0
,@DayGood char(1) = 'y'
,@Datein date = '03/14/2011'
-- Table Variables from ResClosedToArrival
--declare @RMS_ResortID char(10)
-- ,@RMS_RoomType varchar(15)
-- ,@RMS_FromDate date
-- ,@RMS_ToDate date
-- ,@RMS_Sunday int
-- ,@RMS_Monday int
-- ,@RMS_Tuesday int
-- ,@RMS_Wendsday int
-- ,@RMS_Thursday int
-- ,@RMS_Friday int
-- ,@RMS_Saturday int
,@linenum int = 19
set @Dayin = (select DATEPART(dw, @datein))
SELECT [RMS_ResortID]
,RMS_RoomType
,RMS_FromDate
,RMS_ToDate
,RMS_Sunday
,RMS_Monday
,RMS_Tuesday
,RMS_Wednesday
,RMS_Thursday
,RMS_Friday
,RMS_Saturday
FROM dbo.ResClosedToArrival
where RMS_ResortId = 'SWA' and RMS_RoomType = '2BR' and (@Datein >= RMS_FromDate and @Datein <= RMS_ToDate);
select @DayGood =
CASE
WHEN @Dayin = 1 and RMS_Sunday = 1 THEN 'N'
WHEN @Dayin = 2 and RMS_Monday = 1 THEN 'N'
WHEN @Dayin = 3 and RMS_Tuesday = 1 THEN 'N'
WHEN @Dayin = 4 and RMS_Wednesday = 1 THEN 'N'
WHEN @Dayin = 5 and RMS_Thursday = 1 THEN 'N'
WHEN @Dayin = 6 and RMS_Friday = 1 THEN 'N'
WHEN @Dayin = 7 and RMS_Saturday = 1 THEN 'N'
ELSE 'Y'
END;
Print @Dayin & ' ' & @Daygood
- - - messages - - - - - - -
Msg 207, Level 16, State 1, Line 41
Invalid column name 'RMS_Sunday'.
Msg 207, Level 16, State 1, Line 42
Invalid column name 'RMS_Monday'.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'RMS_Tuesday'.
Msg 207, Level 16, State 1, Line 44
Invalid column name 'RMS_Wednesday'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'RMS_Thursday'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'RMS_Friday'.
Msg 207, Level 16, State 1, Line 47
Invalid column name 'RMS_Saturday'.
March 16, 2011 at 7:19 am
The case needs to be in the select itself.
You're getting this error because you have no from clause in the case's select. So sql can't find the columns and throws an error.
March 16, 2011 at 7:24 am
select @DayGood =
CASE
WHEN @Dayin = 1 and RMS_Sunday = 1 THEN 'N'
WHEN @Dayin = 2 and RMS_Monday = 1 THEN 'N'
WHEN @Dayin = 3 and RMS_Tuesday = 1 THEN 'N'
WHEN @Dayin = 4 and RMS_Wednesday = 1 THEN 'N'
WHEN @Dayin = 5 and RMS_Thursday = 1 THEN 'N'
WHEN @Dayin = 6 and RMS_Friday = 1 THEN 'N'
WHEN @Dayin = 7 and RMS_Saturday = 1 THEN 'N'
ELSE 'Y'
END
FROM <some table>
<Some Where clause if necessary>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2011 at 7:56 am
Worked. Don't understand why it can't see the field names from the first select but in any case your suggestion worked. Thanks again.
Mike
March 16, 2011 at 7:58 am
It's not the same query... it's like asking your gps to find New-york.
But you're sitting somewhere on jupiter. Not going to work!
March 16, 2011 at 10:47 am
mgodfrey 54349 (3/16/2011)
Worked. Don't understand why it can't see the field names from the first select but in any case your suggestion worked.
Because each select is independent. The second select has no from clause, therefore, since it is a separate select, not part of the first one, SQL has no idea where the columns that you have specified are supposed to come from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply