Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem?

Incorrect syntax near the keyword 'Declare'

SQL Query:

create View [dbo].StokMaliyet
AS
Declare @Dolar decimal(18,3),@Euro decimal(18,3)

set @Dolar=(select Dolar from GunlukDoviz
set @Euro=(select Euro from GunlukDoviz)

 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*@Dolar)/@Euro
 When dovtip='0' then (net_maliyet)/@Euro
 end )  as Maliyet
 from ib_mlyt
share|improve this question
    
Sql Server? What version Sql Server do you use? –  garik Sep 16 '11 at 11:21
    
Microsoft SQLServer 2008. –  Cell-o Sep 16 '11 at 14:32
add comment

3 Answers

up vote 0 down vote accepted

With a CTE?

This assumes that you get one row from Dolar and Euro. If you get more then one, add a TOP 1 ..ORDER BY: your original query is wrong too then

create View [dbo].StokMaliyet
AS

WITH
    Dolar AS (select Dolar from GunlukDoviz),
    Euro AS (select Euro from GunlukDoviz)
 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*Dolar)/Euro 
 When dovtip='0' then (net_maliyet)/Euro
 end )  as Maliyet
 from ib_mlyt
     CROSS JOIN
      Dolar 
     CROSS JOIN
      Euro 
GO
share|improve this answer
add comment

You cannot use declare and any code manipulation in view (select statement ONLY at SQL Server of course).

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     } 

See for more details

Use stored procedure instead of view

share|improve this answer
    
+1 as you have the been the only one to note that you cannot use DECLARE inside a view –  Andrew Bickerton Sep 16 '11 at 11:05
    
@Andrew Bickerton yes of course, because Cell-o could not describe all own code. If he did, I will applaud to gbn's and Simon's solutions (if its work). :). Thanks. –  garik Sep 16 '11 at 11:26
add comment

You would have to change it to

create View [dbo].StokMaliyet
AS
 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*(select top (1) Dolar from GunlukDoviz))/(select top (1) Euro from GunlukDoviz)
 When dovtip='0' then (net_maliyet)/(select top (1) Euro from GunlukDoviz)
 end )  as Maliyet
 from ib_mlyt

Hope this helps

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.