Hi,
I am generating a bar chart in Apex and my data has few gaps as a result there are gaps between bars.If I fill data in query with zero value then my bar chart will be correctly displayed. Below is data and required output
create table tab1(job varchar2(10),tdate date,value number)
insert into tab1 values('A',to_date('02-APR-19','DD-MON-YY'),100.32)
insert into tab1 values('B',to_date('02-APR-19','DD-MON-YY'),56.3)
insert into tab1 values('C',to_date('02-APR-19','DD-MON-YY'),85.3)
insert into tab1 values('X',to_date('03-APR-19','DD-MON-YY'),10.25)
insert into tab1 values('Y',to_date('03-APR-19','DD-MON-YY'),9.25)
insert into tab1 values('Z',to_date('03-APR-19','DD-MON-YY'),24.25)
A 02-APR-19 100.32
B 02-APR-19 56.3
C 02-APR-19 85.3
X 03-APR-19 10.25
Y 03-APR-19 9.25
Z 03-APR-19 24.25
Output
A 02-APR-19 100.32
B 02-APR-19 56.3
C 02-APR-19 85.3
A 03-APR-19 0
B 03-APR-19 0
C 03-APR-19 0
X 03-APR-19 10.25
Y 03-APR-19 9.25
Z 03-APR-19 24.25
X 02-APR-19 0
Y 02-APR-19 0
Z 02-APR-19 0
I've added one more row for April 5 so we can expand this out more.
SQL> create table tab1(job varchar2(10),tdate date,value number);
Table created.
SQL>
SQL> insert into tab1 values('A',to_date('02-APR-19','DD-MON-YY'),100.32);
1 row created.
SQL> insert into tab1 values('B',to_date('02-APR-19','DD-MON-YY'),56.3);
1 row created.
SQL> insert into tab1 values('C',to_date('02-APR-19','DD-MON-YY'),85.3);
1 row created.
SQL> insert into tab1 values('X',to_date('03-APR-19','DD-MON-YY'),10.25);
1 row created.
SQL> insert into tab1 values('Y',to_date('03-APR-19','DD-MON-YY'),9.25);
1 row created.
SQL> insert into tab1 values('Z',to_date('03-APR-19','DD-MON-YY'),24.25);
1 row created.
SQL> insert into tab1 values('Z',to_date('05-APR-19','DD-MON-YY'),24.25);
1 row created.
So we need
1) the distinct list of jobs
SQL> select distinct job from tab1;
JOB
----------
B
C
Z
A
Y
X
6 rows selected.
2) the low and upper bound of dates
SQL> select min(tdate) start_date, max(tdate) end_date from tab1;
START_DAT END_DATE
--------- ---------
02-APR-19 05-APR-19
1 row selected.
3) which we can then expand out using a trick with CONNECT BY (see video at bottom)
SQL> with date_range as
2 ( select min(tdate) start_date, max(tdate) end_date from tab1 )
3 select start_date+rownum-1 date_val
4 from date_range
5 connect by level <= end_date - start_date + 1;
DATE_VAL
---------
02-APR-19
03-APR-19
04-APR-19
05-APR-19
and then we can bring it all together
SQL> with
2 date_range as
3 ( select min(tdate) start_date, max(tdate) end_date from tab1 ),
4 all_dates as
5 ( select start_date+rownum-1 date_val
6 from date_range
7 connect by level <= end_date - start_date + 1
8 ),
9 job_list as
10 ( select distinct job from tab1 )
11 select *
12 from all_dates,
13 job_list,
14 tab1
15 where job_list.job = tab1.job(+)
16 and all_dates.date_val = tab1.tdate(+)
17 order by 2,1;
DATE_VAL JOB JOB TDATE VALUE
--------- ---------- ---------- --------- ----------
02-APR-19 A A 02-APR-19 100.32
03-APR-19 A
04-APR-19 A
05-APR-19 A
02-APR-19 B B 02-APR-19 56.3
03-APR-19 B
04-APR-19 B
05-APR-19 B
02-APR-19 C C 02-APR-19 85.3
03-APR-19 C
04-APR-19 C
05-APR-19 C
02-APR-19 X
03-APR-19 X X 03-APR-19 10.25
04-APR-19 X
05-APR-19 X
02-APR-19 Y
03-APR-19 Y Y 03-APR-19 9.25
04-APR-19 Y
05-APR-19 Y
02-APR-19 Z
03-APR-19 Z Z 03-APR-19 24.25
04-APR-19 Z
05-APR-19 Z Z 05-APR-19 24.25
24 rows selected.
You can change the "*" on line 11 to select whatever columns you want, and use NVL to map nulls to zero.
Here's a video explaining the history behind the "connect by level" concept