I have two tables exam_table and emp_table. Here I would like to join both tables using left outer join and I want to create a new column called new_column using exam_completed_date column from the right table
select id, exam_completed_date from exam_table;
id exam_completed_date
0 12-01-2019
1 12-12-2019
select id, week_end_date from emp_table where id=0;
id week_end_date
0 11-29-2019
0 11-30-2019
0 12-31-2019
0 12-01-2019
0 12-02-2019
0 12-03-2019
0 12-04-2019
when I am joining two tables using left outer join i am getting ? in the data as shown below,
select id, week_end_date, exam_completed_date ,
max(exam_completed_date) over (partition by id order by week_end_date) as newcolumn
from emp_table emp left outer join
exam_table exam
on (exam.id=emp.id and exam.exam_completed_date=emp.week_end_date)
where id=0
id week_end_date exam_completed_date new_column
0 11-29-2019 ? ?
0 11-30-2019 ? ?
0 12-31-2019 ? ?
0 12-01-2019 12-01-2019 12-01-2019
0 12-02-2019 ? 12-01-2019
0 12-03-2019 ? 12-01-2019
0 12-04-2019 ? 12-01-2019
How can I go about eliminating ?
and replacing with null?
You are using window function & which never return ?
unless you specify, you probably receives null
values, so you can replace with coalesce()
:
coalesce(max(exam_completed_date) over (partition by id order by week_end_date), '')