CODEWITHSUNDEEP
sqlsql-servercase
omar K
omar K

Reputation: 245

How to include Case Statement in SQL Query

I want to incorporate this Case statement in my sql query, but I'm receiving this error: Invalid Column Name: MsgID. Does anyone have any suggestions to as to how the code may look like to run this error free? Please note I am using SQL server management studio 2008.

Case Statement:

    CASE WHEN MsgID = '1' THEN SUBSTRING(Parms,1,6) +' Was removed from:'
    WHEN MsgID = '2' THEN 'This Move List has been cleared of all its media'
    ELSE '' -- Optional Else
    END AS [RemovalPath],

Original Query:

    ;WITH CTE AS
    (
    SELECT  CONVERT(DATETIME,LEFT([Date],8)+' '+
                         SUBSTRING([Date],10,2)+':'+
                         SUBSTRING([Date],12,2)+':'+
                         SUBSTRING([Date],14,2)+'.'+
                         RIGHT([Date],3)) [Date],
        ItemId,
        Parms,
        [OBJECT]
   FROM (  SELECT  ItemId,
                CONVERT(VARCHAR(18),[Date]) [Date],
                Parms,
                [OBJECT]
        FROM JnlList
        WHERE UserCode = 'Automation') A
   )
   SELECT  ItemId,
    CONVERT(DATE,[Date]) [Date],
    CONVERT(VARCHAR(10),[Date],108) AS [Time],
    SUBSTRING(Parms,1,6) +' Was removed from:' RemovalPath,
    [OBJECT] [Move List:]
   FROM CTE
   WHERE [Date] >= CONVERT(DATE,DATEADD(DAY,-4,GETDATE()))

Query That is Giving me the error:

  WITH CTE AS
  (
  SELECT  CONVERT(DATETIME,LEFT([Date],8)+' '+
                         SUBSTRING([Date],10,2)+':'+
                         SUBSTRING([Date],12,2)+':'+
                         SUBSTRING([Date],14,2)+'.'+
                         RIGHT([Date],3)) [Date],
        ItemId,
        MsgID,
        Parms,
        [OBJECT]
 FROM (  SELECT  ItemId,
                CONVERT(VARCHAR(18),[Date]) [Date],
                Parms,
                [OBJECT]
        FROM JnlList
        WHERE UserCode = 'Automation') A
 )
 SELECT  ItemId, MsgID,
    CONVERT(DATE,[Date]) [Date],
    CONVERT(VARCHAR(10),[Date],108) AS [Time],
    CASE WHEN MsgID = '1' THEN SUBSTRING(Parms,1,6) +' Was removed from:'
    WHEN MsgID = '2' THEN 'This Move List has been cleared of all its media'
    ELSE '' -- Optional Else
    END AS [RemovalPath],
    [OBJECT] [Move List:]
 FROM CTE
 WHERE [Date] >= CONVERT(DATE,DATEADD(DAY,-4,GETDATE()))

Upvotes: 1

Views: 764

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You're not exposing MsgId from (presumably) JnlList as A, here:

FROM (  SELECT  ItemId,
            CONVERT(VARCHAR(18),[Date]) [Date],
            Parms,
            [OBJECT]
    FROM JnlList
    WHERE UserCode = 'Automation') A

So you may just need to add MsgId to that SELECT clause. If it's not from that table, tell us where it's meant to be from.

Upvotes: 0

David M
David M

Reputation: 72870

It's not the case statement that's causing the problem, it's the CTE declaration - there is no MsgId column in the inner SELECT statement, so you can't include it in the column list outside.

Upvotes: 3

Related Questions