January 25, 2006 at 12:52 pm
Hello to all. I wonder if I any of you can help me with a simple question. I created a view to read data as per each Friday. I first hard coded the date for testing purposes, but now I am ready to use it in my life ssytem and don't want to have a hard coded date in my view.
If I run the statement in query analyzer it runs fine, but when I tried to alter the view to accept datefirst I got an error message that reads:
Server: Msg 156, Level 15, State 1, Procedure CurrentAmounts, Line 7
Incorrect syntax near the keyword 'SET'.
The statement is as simple as this:
ALETRE VIEW MyView
AS
SET DATEFIRST 1
DECLARE @Date DATETIME
SET @Date = GETDATE()
SELECT DISTINCT TOP 100 PERCENT EmpID,
CodeID, TaxDescription, BranchCode,
CurrentDeductions, CurrentWages, CurrentGrossWages,
CurrentSubjectWages, PayDate
FROM table1 INNER JOIN table2 ON table1.CodeID = table2.CodeID
WHERE (table1.MyDate = CONVERT(VARCHAR(8), DATEADD(DAY, 5 - DATEPART(WEEKDAY, @Date), @Date), 112))
What am I doing wrong!!!! please help. Do I have to turn on or off any setting for the view to accept datefirst? or is just a matter of "user" error on syntax? Thank yoiu
January 25, 2006 at 2:04 pm
A lot of things.
1st of all, don't use SELECT TOP 100 PERCENT in views.
If you need it for ORDER BY don't do it. It's really bad for performance.
Perform all orderings in SP.
2nd, view is single SELECT statements.
No DECLARE or SET allowed.
Your view may return additional column with last Friday before table1.MyDate for any particular line. And you filter by this value when you select from the view.
If you want to have @@Datefirst independent results use this:
(@@Datefirst + DATEPART(WEEKDAY, @AnyDate) -2 )%7 + 1
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=235406
_____________
Code for TallyGenerator
January 25, 2006 at 2:29 pm
Thanks, I will try that. Sorry for the top 100, I took the main statement and copy what I needed. The order by goes in a different place and not within the view.
The datefirst I am using is basically a paramenter to read my data with friday's date. I used date first in another step of the whole process to and return the right date.
I will try to use your @@datefirst as a paramenter to see if that works better. Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply