0

I was working on Oracle APEX writing a query which gives a list of the upcoming birthdays of students in 2016 and orders them chronologically. This worked and I used the following code:

SELECT first_name, last_name, to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy') AS birthday
FROM students
WHERE date_of_birth IS NOT NULL
ORDER BY birthday

This gave the right output and started showing names with the chronologically ordered birthdays in 2016.

However, I now wanted to get another column which shows on which day students celebrate their birthday in class. So for students with a birthday during a week day that would be the same day, but for students with a birthday during the weekend that would be Monday, using a case statement to change 'saturday' or 'sunday' to 'monday'.

I already made a query that shows on which day the birthday of a student is celebrated, which gives proper output like 'friday' or 'wednesday', the code is:

SELECT first_name, last_name, to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy') AS birthday, to_char(to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy'), 'day') AS celebrationday
FROM students
WHERE date_of_birth IS NOT NULL
ORDER BY birthday

I had already done some simple tests with case statement, like adding something when the first name is 'John', like this:

SELECT student_number, first_name,
(CASE first_name
WHEN 'John' THEN 'Check'
END) addition
FROM Students

Which resulted in correct output; for all names other than 'John' the column addition would be null and those with the name would have 'Check' in the column addition.

Now, the main problem starts than when I try to replicate this in order to check whether celebrationday is in the weekend I either get the following error: 'ORA-00904 'celebrationday': invalid identifier', for this piece of code:

SELECT first_name, last_name, to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy') AS birthday, to_char(to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy'), 'day') AS celebrationday,
(CASE celebrationday
WHEN 'saturday' THEN 'monday'
WHEN 'sunday' THEN 'monday'
END) addition
FROM students
WHERE date_of_birth IS NOT NULL
ORDER BY birthday

(Little commentary, I know the case should also contain the other days of the week, but at first I'm interested in getting a proper output from the weekend days)

Or for the following piece of code I just don't get any results in the column addition, just null values.

SELECT first_name, last_name, to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy') AS birthday,
CASE to_char(to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy'), 'day)
WHEN 'saturday' THEN 'monday'
WHEN 'sunday' THEN 'monday'
END) addition
FROM Students
WHERE date_of_birth IS NOT NULL
ORDER BY date_of_birth
  • What do I need to do in order to get the case statement working so that I can change celebrationday ? I hope the code examples provided are clear enough to work with and give you an idea of what is and isn't working.
KRAD
  • 51
  • 2
  • 10

3 Answers3

0

Ah... you are using to_char to return the day. You will need to account for the trailing spaces in your query. The value is essentially typed as char(9).

I think if you trim the result and add an ELSE condition to account for the other days you should be good to go..

    CASE trim(to_char(to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy'), 'day'))
    WHEN 'saturday' THEN 'monday'
    WHEN 'sunday' THEN 'monday'
    else trim(to_char(to_date(concat(to_char(date_of_birth, 'dd-mm'), '-2016'), 'dd-mm-yyyy'), 'day'))
    END addition
knuckles
  • 101
  • 2
  • Thank you for your help, I tried it out and it worked, for several students with their celebration day on weekend days it now shows monday. Could you perhaps further explain what exactly the issue with the trailing spaces was ? – KRAD Oct 17 '15 at 10:22
  • Glad it worked. As per the specification on the using the 'DAY' format element the value returned will be padded with blanks to display width of the widest name of day in the date language used for this element. https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm – knuckles Oct 19 '15 at 22:28
0

In order to refer to celebrationday in the case statement, you need to wrap it in an inner query. select case celebrationday = . . . from ( select . . . as celebrationday . . . ). An inner query to define it and an outer one to respond to it.

Paul Kienitz
  • 878
  • 6
  • 25
0

You can avoid working with day names altogether:

SELECT 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW') day_of_week
, birthday_dt 
  + CASE 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW')
      WHEN 6 THEN 2 
      WHEN 7 THEN 1
      ELSE 0 
    END corrected_dt
, to_char(
  birthday_dt 
  + CASE 1 + TRUNC (birthday_dt) - TRUNC (birthday_dt, 'IW')
      WHEN 6 THEN 2 
      WHEN 7 THEN 1
      ELSE 0 
    END
  , 'fmDay') corrected_day
from 
(
select to_date('19/10/2015','DD/MM/YYYY') birthday_dt from dual --monday
union all
select to_date('20/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('21/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('22/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('23/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('24/10/2015','DD/MM/YYYY') birthday_dt from dual
union all
select to_date('25/10/2015','DD/MM/YYYY') birthday_dt from dual --sunday
);

Run it on on Oracle's LiveSQL tool 

DAY_OF_WEEK CORRECTED_DT CORRECTED_DAY
1           19-OCT-15    Monday
2           20-OCT-15    Tuesday
3           21-OCT-15    Wednesday
4           22-OCT-15    Thursday
5           23-OCT-15    Friday
6           26-OCT-15    Monday
7           26-OCT-15    Monday

Day of week(1 + TRUNC (dt) - TRUNC (dt, 'IW')) taken from: https://community.oracle.com/thread/2207756

Tom
  • 6,988
  • 1
  • 26
  • 40