Incorrect syntax near the keyword 'Select'.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Incorrect syntax near the keyword 'Select'.

Author

Topic

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-05 : 13:10:16

Good Day:

I'm working a reservation Web application and I need to insert missing dates based on calculations using the DATEADD function using SQL Server 2000. I get the following errors:

Server: Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'Select'.
Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'Select'.

My entire script is as follows:
DECLARE @MonthNumber varchar(2)
DECLARE @YearNumber char(4)
DECLARE @CkEndDate DATETIME
---------Gets new StatusStartDate to insert into Status table
DECLARE @NewStartDateAvail DATETIME
---------Gets new StatusEndDate to insert into Status table
DECLARE @NewEndDateAvail DATETIME
---------Used to determine whether a StatusStartDate is missing
DECLARE @StatusStartDate DATETIME
---------Used to determine whether a StatusEndDate is missing
DECLARE @StatusEndDate DATETIME
----For testing only--This will eventually be a parameter passed by ASP.NET
SET @MonthNumber='6'
----For testing only--This will eventuall be a parameter passed by ASP.NET
SET @YearNumber='2007'
----Make Sure the first day of the month is in the Status table
SET @CkEndDate=@MonthNumber + '/1/' + @YearNumber

WHILE @@ROWCOUNT<>0
BEGIN
SET @StatusStartDate=Select StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber
SET @StatusEndDate=Select StatusEndDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber

IF @StatusStartDate<>@CkEndDate
SET @NewEndDateAvail=StatusStartDate
SET @NewStartDateAvail=@CkEndDate
SET @NewEndDateAvail=NewEndDateAvail.AddDays(-1)

SET @CkEndDAte=@StatusEndDate
SET @CkEndDate=DATEADD(d,1,@CkEndDate)-1

Select * from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber
END

Additional information:
My qryStatusMissingDates Sql Syntax is as follows:
SELECT TOP 100 PERCENT DATEPART([month], StatusStartDate) AS MonthNumber, DATEPART([year], StatusEndDate) AS YearNumber, StatusStartDate,
StatusEndDate, StatusType, PropertyMgtID
FROM dbo.Status
WHERE (DATEPART([month], StatusStartDate) = 6) AND (DATEPART([year], StatusEndDate) = 2007)
ORDER BY StatusStartDate

Thank You,
Denise

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 13:12:38

[code]
SET @StatusStartDate=(Select StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)

OR

Select @StatusStartDate = StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber
[/code]

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-05 : 13:55:01

Hello,

Thank you for your suggestion. Now it gives me the following error message:
Invalid column name 'StatusStartDate'

I am confused because I have a column called StatusStartDay within my view called qryStatusMissingDates as follows:

SELECT TOP 100 PERCENT StatusStartDate, StatusEndDate, DATEPART([month], StatusStartDate) AS MonthNumber, DATEPART([year], StatusEndDate)AS YearNumber, StatusType, PropertyMgtID
FROM dbo.Status
ORDER BY StatusStartDate

Thanks,
Densie

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-05 : 14:12:47

Well there are other thiungs that I see may be wrong...are you sure it will only retuurn 1 value?

Read the hint link in my sig and post what it asks for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-05 : 16:16:00

Hello,

Thank you for the helpful links.

Here is some additional information and I hope it helps as follows:

Objective:
Insert missing date ranges to support an ASP.NET reservation Web application.

The table that gets the inserted dates is as follows:
CREATE TABLE [dbo].[Status] (
[StatusID] [int] IDENTITY (1, 1) NOT NULL ,
[StatusStartDate] [smalldatetime] NOT NULL ,
[StatusEndDate] [smalldatetime] NOT NULL ,
[StatusType] [nvarchar] (15) NOT NULL ,
[PropertyMgtID] [smallint] NULL
) ON [PRIMARY]

Test Data:
2421 6/1/2007 6/2/2007 Pending 79
2423 6/3/2007 6/7/2007 Available 79
2417 6/8/2007 6/14/2007 Unavailable 79
2422 6/22/2007 6/28/2007 Pending 79
2424 6/29/2007 6/30/2007 Available 79
Notice how there is a date range missing between StatusID 2417 and StatusID 2422. The missing date range is StatusStartDate=6/15/2007 and StatusEndDate=6/21/2007. And these are the two date range values that need to be inserted into the Status table. The literal values that need to be inserted into the Status table are StatusType=’Available’ and PropertyMgtID=’79’’

I am trying to write a script that inserts missing date ranges by month. The script should read each record from the following view called qryStatusMissingDates based on two parameters as follows:
DECLARE @MonthNumber varchar(2)
DECLARE @YearNumber char(4)

----For testing only--This will eventually be a parameter passed by ASP.NET using a stored procedure.
SET @MonthNumber='6'
SET @YearNumber='2007'

The qryStatusMissingDates view SQL Syntax is as follows:
SELECT TOP 100 PERCENT StatusStartDate, StatusEndDate, DATEPART([month], StatusStartDate) AS MonthNumber, DATEPART([year], StatusEndDate)
AS YearNumber, StatusType, PropertyMgtID
FROM dbo.Status
ORDER BY StatusStartDate
The Sql Script should read the first record and compare the dates with the following SQL syntax:

DECLARE @StatusStartDate DATETIME
DECLARE @CkEndDate DATETIME

SET @CkEndDate=@MonthNumber + '/1/' + @YearNumber

SET @StatusStartDate=(Select StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)

IF @StatusStartDate<>@CkEndDate ---then insert the missing values

SET @NewStartDateAvail=@CkEndDate
SET @NewEndDateAvail=@StatusStartDate
SET @NewEndDateAvail= DATEADD(d,1@ NewEndDateAvail)-1
SET @CkEndDate=@StatusEndDate
SET @CkEndDate=DATEADD(d,1,@CkEndDate)-1

INSERT INTO STATUS (StatusStartDate, StatusEndDate,StatusType,PropertyMgtID)
VALUES (@NewStartDateAvail, @NewEndDateAvail, 'Available','79')

---Now the script should get the @StatusEndDate value to create the date ranges as follows:

SET @StatusEndDate=(Select StatusEndDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)

@CkEndDate=@StatusStartDate
@CkEndDate=DateAdd(d, 1, dCkEndDate)

END
===============================================================
New Error:
Now I am getting a new error as follows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The syntax that has the error is as follows:
SET @StatusStartDate=(Select StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)

My entire SQL Script is as follows:
DECLARE @MonthNumber varchar(2)
DECLARE @YearNumber char(4)
DECLARE @CkEndDate DATETIME
DECLARE @NewStartDateAvail DATETIME
DECLARE @NewEndDateAvail DATETIME
DECLARE @StatusStartDate DATETIME
DECLARE @StatusEndDate DATETIME
----For testing only--This will eventually be a parameter passed by ASP.NET
SET @MonthNumber='6'
----For testing only--This will eventuall be a parameter passed by ASP.NET
SET @YearNumber='2007'
----Make Sure the first day of the month is in the Status table as 6/1/2007 (Test Data)

SET @CkEndDate=@MonthNumber + '/1/' + @YearNumber
WHILE @@ROWCOUNT<>0
BEGIN

SET @StatusStartDate=(Select StatusStartDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)
SET @StatusEndDate=(Select StatusEndDate from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber)

IF @StatusStartDate<>@CkEndDate
SET @NewStartDateAvail=@CkEndDate
SET @NewEndDateAvail=@StatusStartDate
SET @NewEndDateAvail=DATEADD(d,1,@NewEndDateAvail)-1
SET @CkEndDate=@StatusEndDate
SET @CkEndDate=DATEADD(d,1,@CkEndDate)-1

INSERT INTO STATUS (StatusStartDate, StatusEndDate,StatusType,PropertyMgtID)
VALUES (@NewStartDateAvail, @NewEndDateAvail, 'Available','79')
Select * from qryStatusMissingDates where MonthNumber=@MonthNumber and YearNumber=@YearNumber
END

Thanks,
Denise

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 18:03:58

Is this the same task?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81685

Peter Larsson
Helsingborg, Sweden

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-05 : 18:21:05

Yes, it was the simplified version and then I realized I needed to post more information.

DeniseGoodheart
Starting Member

12 Posts

Posted - 2007-04-07 : 15:11:35

Hi All,

This question has been resolved, but I do not know what is the procedure here for closing a resolved questioned. My syntax that works is as follows:

select date_start_missing, min(date_end_missing) as date_end_missing
from (
select d_begin.StatusEndDate + 1 as date_start_missing, d_end.StatusStartDate as date_end_missing
from dbo.status d_begin
join (select min(StatusStartDate)-1 as StatusStartDate, StatusEndDate from dbo.status group by StatusEndDate) d_end on (d_begin.StatusEndDate + 1 < d_end.StatusStartDate)
where d_begin.StatusEndDate + 1 not in (select StatusStartDate from dbo.status)
) a
group by date_start_missing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources