Oracle SQL - SELECT CASE WHEN column = ( select statement)

user_8882837 0
Viewed 5335

I want to select "up_year" with SQL case expression.

if I do this its ok:

SELECT CASE WHEN column1  = '2020' then 'up_year' else 'past_year' else end ;

but when trying to do like this : do not want to change every year.

SELECT 
CASE WHEN column1  = (select extract(year from sysdate) from dual) 
then 'up_year' 
else 'past_year' else end ;
case-when oracle sql
2 Answers

You can use with clause, for example

  with y as (select extract(year from sysdate) year from dual)
  SELECT CASE WHEN column1  = y.year then 'up_year' else 'past_year'  end from y -- and your table

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement

You can separate to two steps with select into a variable the extract output and then use it

user_72949000

You simply want to match for the current year. Then one option would be using TO_CHAR(,'yyyy') :

SELECT CASE
         WHEN column1 = TO_CHAR(sysdate,'yyyy') then
          'up_year'
         ELSE
          'past_year'
       END AS "My Year"
  FROM tab
user_58413060
Related