主页 / dba / 问题 / 5840
Accepted
Cell-o
Asked: 2011-09-17 02:28:11 +0800 CST

SQL查询声明问题

  • 772

我有一个如下所示的视图。视图运行良好。当我尝试运行视图时，出现以下错误。问题可能是什么？

Incorrect syntax near the keyword 'Declare'

SQL查询：

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
sql-server-2008

4 个回答

  1. 您不能declare在视图中使用和任何代码操作（当然，只能在 SQL Server 中选择语句）。

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

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

    详情请见

    使用存储过程而不是视图

    • 5

  2. 你必须把它改成

    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

    希望这可以帮助

    • 2
  3. Best Answer

    有 CTE？

    这假设您从 Dolar 和 Euro 获得一行。如果您得到的不止一个，请添加一个 TOP 1 ..ORDER BY: 您的原始查询也是错误的

    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
    • 1

  4. 您可以使用笛卡尔乘积，CTE因为我们CTE只有一行。

    create View [dbo].StokMaliyet
AS
WITH
    CTEDolar AS (select top 1 cast (Dolar  as decimal(8,7) )Dolar from GunlukDoviz),
    CTEEuro AS (select top 1 cast (Euro  as decimal(8,7) ) Euro  from GunlukDoviz)
 select Stok_Kodu,
 (case
 **strong text**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,CTEDolar as CTEDolar ,CTEEuro as CTEEuro

GO
    • -1

相关问题