时间戳

首页 > 解决方案 > SQL Server：带案例查看

sql - SQL Server：带案例查看

问题描述

我正在尝试在 SQL Server 2014 中创建一个视图，但是当我执行 SQL 语句时，我收到了这个错误：

列名“MONTHNUMBER”无效

我的声明：

CREATE VIEW [Metadata].[NEW_VIEW]
AS
    SELECT   
        dbo.fnFirstDayOfMonth(a.Year,a.Month) AS FR_OBS_DATE,
        b.Code AS COUNTRY,
        a.Groups AS COMMODITY,
        a.ImpValue AS OBS_VALUE,
        a.DexValue AS OBS_VALUE_2,
        a.RexValue AS OBS_VALUE_3,
        a.TexValue AS OBS_VALUE_4,
        0 AS UNIT_MULT,
        'NUM' AS UNIT_MEASURE,
        a.Month AS MONTHNUMBER,
        CASE a.Month
                WHEN 'January' THEN 1
                WHEN 'February' THEN 2
                WHEN 'March' THEN 3
                WHEN 'April' THEN 4
                WHEN 'May' THEN 5
                WHEN 'June' THEN 6
                WHEN 'July' THEN 7
                WHEN 'August' THEN 8
                WHEN 'September' THEN 9
                WHEN 'October' THEN 10
                WHEN 'November' THEN 11
                WHEN 'December' THEN 12
        END,
        CAST(a.Year AS varchar(10)) + '-' + CAST(MONTHNUMBER AS varchar(10)) AS TIME_PERIOD
    FROM 
        [Trade].[HS] a
    INNER JOIN 
        [Codelist].[Countries] b ON a.CountryName = b.Name

我会很感激关于我做错了什么的指示。

标签： sqlsql-server

解决方案

试试这个，MONTHNUMBER是 的别名a.Month，你不能在那里使用别名：

CREATE VIEW [Metadata].[NEW_VIEW]
AS
SELECT   --dbo.fnFirstDayOfMonth(a.Year,a.Month) AS FR_OBS_DATE, I think your problem is here
        b.Code AS COUNTRY,
        a.Groups AS COMMODITY,
        a.ImpValue AS OBS_VALUE,
        a.DexValue AS OBS_VALUE_2,
        a.RexValue AS OBS_VALUE_3,
        a.TexValue AS OBS_VALUE_4,
        0 AS UNIT_MULT,
        'NUM' AS UNIT_MEASURE,

        a.Month AS MONTHNUMBER,
            CASE a.Month
                WHEN 'January' THEN '1'
                WHEN 'February' THEN '2'
                WHEN 'March' THEN '3'
                WHEN 'April' THEN '4'
                WHEN 'May' THEN '5'
                WHEN 'June' THEN '6'
                WHEN 'July' THEN '7'
                WHEN 'August' THEN '8'
                WHEN 'September' THEN '9'
                WHEN 'October' THEN '10'
                WHEN 'November' THEN '11'
                WHEN 'December' THEN '12'
            END AS MonthName,

        CAST(a.Year AS varchar(10)) + '-' + 
              CASE a.Month
                WHEN 'January' THEN '1'
                WHEN 'February' THEN '2'
                WHEN 'March' THEN '3'
                WHEN 'April' THEN '4'
                WHEN 'May' THEN '5'
                WHEN 'June' THEN '6'
                WHEN 'July' THEN '7'
                WHEN 'August' THEN '8'
                WHEN 'September' THEN '9'
                WHEN 'October' THEN '10'
                WHEN 'November' THEN '11'
                WHEN 'December' THEN '12'
            END AS TIME_PERIOD

FROM [Trade].[HS] a
INNER JOIN [Codelist].[Countries] b
    ON a.CountryName = b.Name
GO

推荐阅读