About hierarchical queries, filling the gaps and doing stuff in ApEx

Sometimes you are writing some codes and things really come together well.

Let’s imagine, you are writing a little tool in ApEx for registering hours on a project. The hours are recorded in a table which contains all days of the week and a week number (and employeenumber and projectnumber). One region on the page shows all weeks in the vicinity of the current date, including those which have no recorded hours and thus do not exist in the registered_hours table.

First challenge: show all weeks from a few weeks ago to some time in the future. Thank goodness Oracle introduced the pseudocolumn LEVEL (I called it an analytical function earlier, but Alex pointed out correctly it’s not!), because that is just what we need. One select statement on the dual table is enough to create zillions of records, from any period of time we like:

1 SELECT to_number( to_char( To_Date( To_Char(SYSDATE,’YYYYMM’)||’01’
2 ,’YYYYMMDD’) + (LEVEL*7) – 35
3 , ‘yyyyiw’
4 )
5 ) period
6 FROM dual
7* CONNECT BY LEVEL <= 100
DEMO> /

PERIOD
———-
200705
200706
200707
200708
200709
[..]
200848
200849
200850
200851

PERIOD
———-
200852

100 rows selected.

That’s a good thing to start with, we can create any set of weeknumbers we like.

But, for some weeks there are records in the registered_hours, and we want to use the above query only to ‘fill the gaps’.
To display the registered hours, we have a simple query:

select year_week period
, sum( nvl(maandag, 0 )
+nvl(dinsdag,0)+nvl(woensdag,0)+nvl(donderdag,0)
+nvl(vrijdag,0)+nvl(zaterdag,0)+nvl(zondag, 0)) totals
from registered_hours dec
where mdw_num = P0_MDW_NUM
group by year_week;

So we have two resultsets. But how do we combine them? A simple union does not give the proper result, as totals will be always 0 for the first query. And minus doesn’t do the trick either, because this will only make the gaps bigger!
Combining the two brings us better results:

select * from
( select year_week period
, sum( nvl(maandag, 0 )
+nvl(dinsdag,0)+nvl(woensdag,0)+nvl(donderdag,0)
+nvl(vrijdag,0)+nvl(zaterdag,0)+nvl(zondag, 0)) totaal
from registered_hours dec
where mdw_num = P0_MDW_NUM
group by year_week
UNION
select period
, 0 totaal
from ( SELECT to_number( to_char( To_Date( To_Char(SYSDATE,’YYYYMM’)||’01’
,’YYYYMMDD’) + (LEVEL*7) – 35
, ‘yyyyiw’
)
) period
FROM dual
CONNECT BY LEVEL <= 100
MINUS
select year_week period
from registered_hours dec
where mdw_num = P0_MDW_NUM
)
order by period desc nulls last
);

The first select in the UNION is the resultset in the registered_hours table and returns real data.
The second select is the result of the set of weeknumbers MINUS the weeknumbers we already have. This set resembles exactly the gaps we had in our data.
UNION them together (we might even have used UNION ALL of course) and we’re done!

Well, almost. Wouldn’t it be nice if we showed a little indicator in ApEx which record is the current one? You probably seen this a thousand times before, but it’s such an elegant example of the little tricks you can use in ApEx, like coding html tags in the query. So we add an extra column to our query which shows an image where the period in the selection matches the current period in the page.
… select CASE year_week WHEN :P3_CURRENT_PERIOD THEN ‘<img src=”&IMGDIR.arrow.gif />”‘ ELSE ‘ ‘ as “x” …

And the final query looks like this:

select * from

  ( select CASE year_week WHEN P3_CURRENT_PERIOD THEN '<img src="&IMGDIR.arrow.gif"' ELSE ' ' as "x"

    , year_week   period

    , sum( nvl(maandag, 0 )

      +nvl(dinsdag,0)+nvl(woensdag,0)+nvl(donderdag,0)

      +nvl(vrijdag,0)+nvl(zaterdag,0)+nvl(zondag, 0)) totaal

    from   registered_hours dec

    where  mdw_num = P0_MDW_NUM

    group by year_week

      UNION

    select CASE year_week WHEN P3_CURRENT_PERIOD THEN '<img src="&IMGDIR.arrow.gif"' ELSE ' ' as "x"

    , period

    , 0 totaal

    from ( SELECT to_number( to_char( To_Date( To_Char(SYSDATE,'YYYYMM')||'01'

                                             ,'YYYYMMDD') + (LEVEL*7) - 35

                                    , 'yyyyiw'

                                    )

                            ) period

           FROM dual

           CONNECT BY LEVEL <= 100

             MINUS

           select year_week   period

           from registered_hours dec

           where mdw_num = P0_MDW_NUM

         )

    order by period desc nulls last

  );

showing this in ApEX:

So far, another day at the office!