locked
CASE STATEMENT AS A CONDITIONAND ALIAS RRS feed

  • Question

  • HI

    SO I WANT SOMETHING LIKE THIS

    SELECT id,

      CASE WHEN ITEM IN(10,20,30) THEN FINAL_DATE END AS FINAL_DATE33,

    NAME

    FROM EMP

    WHERE FINAL_DATE33 >01/01/2012

    hw to do it

    • Changed type Jingyang Li Wednesday, July 26, 2017 11:06 PM
    Wednesday, July 26, 2017 10:04 PM

Answers

  • You cannot reference a column defined in the SELECT clause (FINAL_DATE33 in your case) in a WHERE clause in the same SELECT.  That's because the WHERE is done before the SELECT so FINAL_DATE33 doesn't exist when the WHERE is calculated.  The workaround is to use a CTE.  So

    ;WITH cte AS
    (SELECT id,
      CASE WHEN ITEM IN(10,20,30) THEN FINAL_DATE END AS FINAL_DATE33,
      NAME
    FROM EMP)
    SELECT id, 
      FINAL_DATE33, 
      NAME
    FROM cte
    WHERE FINAL_DATE33 > '01/01/2012'

    Also note that date literals must be enclosed in single quotes.

    Tom

    • Marked as answer by coool_sweet Wednesday, July 26, 2017 11:22 PM
    Wednesday, July 26, 2017 11:13 PM

All replies

  • Can you please explain further what you need? You can also provided your expected result. Thank you.
    Wednesday, July 26, 2017 10:15 PM
  • SELECT id,

      CASE WHEN ITEM IN(10,20,30) THEN FINAL_DATE  Else NULL END AS FINAL_DATE33,

    NAME

    FROM EMP

    WHERE FINAL_DATE >'01/01/2012'


    • Edited by Jingyang Li Wednesday, July 26, 2017 11:06 PM
    Wednesday, July 26, 2017 11:05 PM
  • You cannot reference a column defined in the SELECT clause (FINAL_DATE33 in your case) in a WHERE clause in the same SELECT.  That's because the WHERE is done before the SELECT so FINAL_DATE33 doesn't exist when the WHERE is calculated.  The workaround is to use a CTE.  So

    ;WITH cte AS
    (SELECT id,
      CASE WHEN ITEM IN(10,20,30) THEN FINAL_DATE END AS FINAL_DATE33,
      NAME
    FROM EMP)
    SELECT id, 
      FINAL_DATE33, 
      NAME
    FROM cte
    WHERE FINAL_DATE33 > '01/01/2012'

    Also note that date literals must be enclosed in single quotes.

    Tom

    • Marked as answer by coool_sweet Wednesday, July 26, 2017 11:22 PM
    Wednesday, July 26, 2017 11:13 PM