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

Question

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 ;

You don't need "select ... from dual" in the right-hand side of the equation. Write it as "case when column1 = extract(year from sysdate)" — user5683823
– user5683823, Commented Oct 22, 2020 at 13:33
What does PL/SQL have to do with this? What you have shown is plain SQL. Please understand that PL/SQL is not another name for "Oracle SQL". Also: plain SQL only has case expressions, not case statements, and indeed everything in your post is case expressions. (CASE statements do exist - in PL/SQL!) I will edit your post to make these corrections; if I misunderstood, you can change back. — user5683823
– user5683823, Commented Oct 22, 2020 at 13:37
Most importantly: What is your question, really? Are you getting an error, and you don't understand why? That has nothing to do with the sub-SELECT; rather, you have an incorrect "else" right before "end". You already have the correct "else" in the expression. You would still be better off not using a sub-SELECT, but that is not what is causing an error in your current statement. — user5683823
– user5683823, Commented Oct 22, 2020 at 13:40

Ori Marko · Accepted Answer · 2020-10-22 13:31:49Z

3

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

edited Oct 22, 2020 at 13:31

answered Oct 22, 2020 at 13:23

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ömer Faruk Over a year ago

thank you but can i do like a=select extract(year from sysdate) , and then select a from dual ;

Ori Marko Over a year ago

@ÖmerFaruk you can also select into to store in a variable

user5683823 Over a year ago

select into is not needed; the OP is not doing anything in PL/SQL, despite what he seems to suggest. It all looks like plain SQL.

Ori Marko Over a year ago

@mathguy I added because he asked and wrote PLSQL in first letters of title

user5683823 Over a year ago

Understood - which is why I edited his post to remove all references to PL/SQL. The entire original post made it pretty clear that it had nothing to do with PL/SQL.

Barbaros Özhan · Accepted Answer · 2020-10-22 13:31:45Z

2

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

answered Oct 22, 2020 at 13:31

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

user5683823 Over a year ago

This is almost right. However, if we give the OP the benefit of the doubt and we assume that the data type of COLUMN1 is NUMBER (since he is comparing to a number in his attempt), then we should compare to a number. You could wrap your right-hand side within TO_NUMBER, but EXTRACT (YEAR FROM...) is more direct.

Barbaros Özhan Over a year ago

Thanks @mathguy , I also thought previously(to wrap within TO_NUMBER()), but considering the quoted value('2020') within the query, I didn't want to mess it up with TO_NUMBER()(seems as if redundant)

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related