Friday 12 January 2018

Data driven APEX icons

We have an application written with a heavily customised Theme 25 built for 10" tablets, and we feel the Universal Theme justifies the move, in part because of the surrounding ecosystem. Check out this forum discussion on the topic.

We've been looking through the packaged applications for applied ideas, and using the Universal Theme sample application as a component reference.

I came across a requirement where we had a list of items that indicated completion level out of 100.
Then I thought about a group of pie icons I saw in Dick Dral's Font APEX icon reference, and knew what I wanted to do.

All I needed to do was round a bunch of numbers to the nearest 5.

To solve such as problem, I usually start by giving myself a bunch of numbers to play with
select rownum rn from dual connect by level <= 100

Then defining that as a WITH statement, so I can refer to the derived column as often as I like.
with nbrs as (
  select rownum rn from dual connect by level <= 100
select rn
 , floor(rn/100 * 20) / 20*100 rnd
 ,'fa fa-pie-chart-'||floor(rn/100 * 20) / 20*100 icon
from nbrs
where mod(rn,5)=0
And I used a divide/multiply by 20 math trick to do the rounding.

We can then feed the result as the relevant icon into whatever region template we need.

I like pie.

Simple, yet effective.


roadling said...

great idea.

you can probably simplify by using 5 as the multiple and divisor of your rnd.

with rownums as
(select rownum rn, decode(mod(rownum, 5), 0, rownum, (5 * trunc((rownum / 5), 0)) + 5) as rnd
from dual
connect by level <= 100)
select rn,
'fa fa-pie-chart-' || rnd as icon
from rownums
--where mod(rn,5) = 0 -- add where clause to go by 5s only

Scott Wesley said...

Simplify? Or complexity moved?

Scott Wesley said...

Just out of curiosity, I tried comparing the two queries. Took a lot of iterations to see any difference.

1.86 secs (.000186 secs per iteration) -- yours
1.34 secs (.000134 secs per iteration) -- mine

1.81 secs (.000181 secs per iteration)
1.41 secs (.000141 secs per iteration)

1.80 secs (.00018 secs per iteration)
1.39 secs (.000139 secs per iteration)