none
Help in query RRS feed

  • Question

  • Hello community Im seeking help to see why this CASE formula is not working is giving me an error in the very first line from the CASE

    Msg 207, Level 16, State 1, Line 35
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 36
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 37
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 38
    Invalid column name 'DIM_Date'.

    can someone tell me what im doing wrong it seems that this is looking for that column and I was in the understanding that the CASE formula will create that

    SELECT [Id]
          ,[Date]
          ,[Day]
          ,[Month]
          ,[FirstOfMonth]
          ,[MonthName]
          ,[Week]
          ,[FirstOfWeek]
          ,[IsoWeek]
          ,[WeekDay]
          ,[Quarter]
          ,[Year]
          ,[FirstOfYear]
          ,[DayOfWeek]
          ,[DayOffset]
          ,[WeekOffset]
          ,[MonthOffset]
          ,[QuarterOffset]
          ,[YearOffset]
          ,[IsThisWeek]
          ,[IsThisMonth]
          ,[IsThisQuarter]
          ,[IsThisYear]
          ,[IsToday]
          ,[IsYesterday]
          ,[IsTomorrow]
          ,[IsInFuture]
          ,[IsLapsed]
          ,[IsLapsing]
          ,[LapseStatus]
    ,

      CASE
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-2 THEN '2 Yrs Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-3 THEN '3 Yrs Prior Calendar Year'
    ELSE 'Other Calendar Year' 
    END AS [Calendar Year Status]
      FROM [DIM_Date]


    guaya81

    Thursday, July 11, 2019 11:22 PM

Answers

  • Hi guaya81,

     

    I checked your query and there's nothing wrong.

    However, you missed [DIM_Date] in CREATE TABLE + INSERT statements. In your description, [DIM_Date] is the table name, but it is also a column name which should be defined in DDL cuz you use 'year' function. 

    --here I create the table [DIM_Date]with a column [DIM_Date]
    IF OBJECT_ID('DIM_Date') IS NOT NULL drop table DIM_Date
    create table DIM_Date ( 
    [Id] int
    ,[Day] int
    ,[Month] int
    ,[DIM_Date]date
    )
    go
    insert into DIM_Date values
    (1,1,2,'2019-02-01'),
    (2,9,5,'2018-05-09'),
    (3,25,12,'2016-12-25'),
    (4,23,11,'2017-11-23')
    
    SELECT [Id],[Day],[Month],
    CASE
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-2 THEN '2 Yrs Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-3 THEN '3 Yrs Prior Calendar Year'
    ELSE 'Other Calendar Year' 
    END AS [Calendar Year Status]
      FROM [DIM_Date]
    /*
    Id          Day         Month       Calendar Year Status
    ----------- ----------- ----------- -------------------------
    1           1           2           Current Calendar Year
    2           9           5           Prior Calendar Year
    3           25          12          3 Yrs Prior Calendar Year
    4           23          11          2 Yrs Prior Calendar Year
    */
    
    --if I create the table [DIM_Date] without a column [DIM_Date]
    IF OBJECT_ID('DIM_Date') IS NOT NULL drop table DIM_Date
    create table DIM_Date ( 
    [Id] int
    ,[Day] int
    ,[Month] int
    )
    go
    insert into DIM_Date values
    (1,1,2),
    (2,9,5),
    (3,25,12),
    (4,23,11)
    
    SELECT [Id],[Day],[Month],
    CASE
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-2 THEN '2 Yrs Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-3 THEN '3 Yrs Prior Calendar Year'
    ELSE 'Other Calendar Year' 
    END AS [Calendar Year Status]
      FROM [DIM_Date]
    /*
    Msg 207, Level 16, State 1, Line 51
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 52
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 53
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 54
    Invalid column name 'DIM_Date'.
    */ 
    

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guaya81 Saturday, July 13, 2019 9:18 PM
    Friday, July 12, 2019 3:01 AM

All replies

  • Hi Guaya81 , 

    Does your table has this column 'DIM_Date' ?

    Thursday, July 11, 2019 11:38 PM
  • DIM_Date is the table 

    the CASE calculation I copy from another exercise and just modify the YEAR(DIM_DATE) part


    guaya81

    Thursday, July 11, 2019 11:44 PM
  • As in the case statement , ' YEAR(DIM_Date)' is checking year of column 'DIM_Date' which is not there , hence it is throwing error.

    Please replace 'DIM_Date' with a valid date column.

    Thanks

    please mark as answer if it resolves your query.

    Friday, July 12, 2019 12:23 AM
  • Hi guaya81,

     

    I checked your query and there's nothing wrong.

    However, you missed [DIM_Date] in CREATE TABLE + INSERT statements. In your description, [DIM_Date] is the table name, but it is also a column name which should be defined in DDL cuz you use 'year' function. 

    --here I create the table [DIM_Date]with a column [DIM_Date]
    IF OBJECT_ID('DIM_Date') IS NOT NULL drop table DIM_Date
    create table DIM_Date ( 
    [Id] int
    ,[Day] int
    ,[Month] int
    ,[DIM_Date]date
    )
    go
    insert into DIM_Date values
    (1,1,2,'2019-02-01'),
    (2,9,5,'2018-05-09'),
    (3,25,12,'2016-12-25'),
    (4,23,11,'2017-11-23')
    
    SELECT [Id],[Day],[Month],
    CASE
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-2 THEN '2 Yrs Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-3 THEN '3 Yrs Prior Calendar Year'
    ELSE 'Other Calendar Year' 
    END AS [Calendar Year Status]
      FROM [DIM_Date]
    /*
    Id          Day         Month       Calendar Year Status
    ----------- ----------- ----------- -------------------------
    1           1           2           Current Calendar Year
    2           9           5           Prior Calendar Year
    3           25          12          3 Yrs Prior Calendar Year
    4           23          11          2 Yrs Prior Calendar Year
    */
    
    --if I create the table [DIM_Date] without a column [DIM_Date]
    IF OBJECT_ID('DIM_Date') IS NOT NULL drop table DIM_Date
    create table DIM_Date ( 
    [Id] int
    ,[Day] int
    ,[Month] int
    )
    go
    insert into DIM_Date values
    (1,1,2),
    (2,9,5),
    (3,25,12),
    (4,23,11)
    
    SELECT [Id],[Day],[Month],
    CASE
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP) THEN 'Current Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-1 THEN 'Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-2 THEN '2 Yrs Prior Calendar Year'
    WHEN YEAR(DIM_Date) = YEAR(CURRENT_TIMESTAMP)-3 THEN '3 Yrs Prior Calendar Year'
    ELSE 'Other Calendar Year' 
    END AS [Calendar Year Status]
      FROM [DIM_Date]
    /*
    Msg 207, Level 16, State 1, Line 51
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 52
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 53
    Invalid column name 'DIM_Date'.
    Msg 207, Level 16, State 1, Line 54
    Invalid column name 'DIM_Date'.
    */ 
    

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guaya81 Saturday, July 13, 2019 9:18 PM
    Friday, July 12, 2019 3:01 AM