CODEWITHSUNDEEP
sqloracle-databasecase-when
Ömer Faruk
Ömer Faruk

Reputation: 111

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

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 ;

Upvotes: 2

Views: 21516

Answers (2)

Ori Marko
Ori Marko

Reputation: 58772

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

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 2

Related Questions