Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: April 09, 2019 - 10:22 am UTC

Last updated: April 10, 2019 - 10:33 am UTC

Version: 18c

Viewed 1000+ times

You Asked

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

and Connor said...

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



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Creating dummy data for chart

Girish Jahagirdar, April 10, 2019 - 4:38 am UTC

An excellent and clean solution to problem
Connor McDonald
April 10, 2019 - 10:33 am UTC

Glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.