[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

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,getdate())) as varchar(2)),2) + '-' + cast(YEAR(DATEADD(m,-6,getdate())) as varchar(4))
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]), 101)  >= CONVERT(VARCHAR(10),(@dt1) , 103)

GROUP BY [FC], month ([FD])
ORDER BY S,D
0
chrislindsay
Asked:
chrislindsay
1 Solution
 
tigin44Commented:
use it like this
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]), 101)  >= CONVERT(VARCHAR(10),('01-' + right('00' + cast(month(DATEADD(m,-6,getdate())) as varchar(2)),2) + '-' + cast(YEAR(DATEADD(m,-6,getdate())) as varchar(4))) , 103)

GROUP BY [FC], month ([FD])
ORDER BY S,D

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
so as tigin44 has shown in his example
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.
0
 
lofCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
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
0
 
chrislindsayAuthor Commented:
Thanks for your help, after the change the View works well.  Thanks to all the other comments which were useful
0
 
chrislindsayAuthor Commented:
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!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now