Cannot save query as a View in sql 2008
Hi Experts,
I have a piece of code I would like to save as a VIEW but when I try to save the VIEW, I receive an error gui
"Incorrect syntax near the key word 'declare'"
A second gui then appears
User canceled out of save dialog
(MS Visual Database Tools)
Can I save a VIEW with a declare statement?
The code I used is :
declare @dt1 as datetime, @dt2 as datetime
set @dt1 = '01-' + right('00' + cast(month(DATEADD(m,-6,ge
set @dt2 = '01-' + right('00' + cast(month(getdate()) as varchar(2)),2) + '-' + cast(YEAR(getdate()) as varchar(4))
SELECT TOP (100) PERCENT [FC] AS S ,month ([FD]) as D, SUM([FP]) AS F, SUM([CFPC]) AS C, SUM([TOB]) AS T
FROM dbo.OPS
WHERE ([CFPC] > 0) AND CONVERT(VARCHAR(10),([FD])
GROUP BY [FC], month ([FD])
ORDER BY S,D
I have a piece of code I would like to save as a VIEW but when I try to save the VIEW, I receive an error gui
"Incorrect syntax near the key word 'declare'"
A second gui then appears
User canceled out of save dialog
(MS Visual Database Tools)
Can I save a VIEW with a declare statement?
The code I used is :
declare @dt1 as datetime, @dt2 as datetime
set @dt1 = '01-' + right('00' + cast(month(DATEADD(m,-6,ge
set @dt2 = '01-' + right('00' + cast(month(getdate()) as varchar(2)),2) + '-' + cast(YEAR(getdate()) as varchar(4))
SELECT TOP (100) PERCENT [FC] AS S ,month ([FD]) as D, SUM([FP]) AS F, SUM([CFPC]) AS C, SUM([TOB]) AS T
FROM dbo.OPS
WHERE ([CFPC] > 0) AND CONVERT(VARCHAR(10),([FD])
GROUP BY [FC], month ([FD])
ORDER BY S,D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you need to use variables and more complicated features you should use table-valued function rather than view. Small change in naming and CREATE syntax but you query it almost exactly the same.
http://technet.microsoft.com/en-us/library/ms191165.aspx
but SETs do not look/perform good so it may be worth changing it to something what tigin44 suggested this time (and whenever possible.
http://technet.microsoft.com/en-us/library/ms191165.aspx
but SETs do not look/perform good so it may be worth changing it to something what tigin44 suggested this time (and whenever possible.
Also, doing the following kludge:
SELECT TOP 100 PERCENT
...
ORDER BY S, D
May have worked in MS Access and earlier versions of MS SQL Server, but (thankfully) is totally pointless in MS SQL Server 2005/2008
SELECT TOP 100 PERCENT
...
ORDER BY S, D
May have worked in MS Access and earlier versions of MS SQL Server, but (thankfully) is totally pointless in MS SQL Server 2005/2008
ASKER
Thanks for your help, after the change the View works well. Thanks to all the other comments which were useful
ASKER
One note of comment administrators. Accepted solution can not be seen in experts-exchange/mobile on iphone as maybe because of format issues of code snippet box.
Thanks for all the other comments Experts, much appresiated!
Thanks for all the other comments Experts, much appresiated!
the answer to your question of "Can I save a VIEW with a declare statement?" is NO, sorry. You will have to replace any references to your @dt1 and @dt2 with the expressions you have in your set statements.