Unpivoting Oracle APEX meta-data

There are APEX dictionary views for most of the data that represents the 'source' that is your APEX application meta-data.

Note: This post has beeen updated to reflect me not looking very hard, but I added a performance comparison... just because.

Today I found one place where I really wanted to run a query to find references to potential data - application substitution strings.

APEX Application Substitution Strings

And yes, I have a CSS rule to right-align those names, to make it easier to read.

I couldn't find any references in the APEX dictionary (correction, see below), so I looked in the Oracle data dictionary to find where it may live.

select * from all_tab_columns 
where column_name like 'SUB%03';

So not only are these stored in a place inaccessible to us mere-mortal developers, they are also stored in 20 sets of name/value columns - not rows.

This means if you have a dozen applications, with references in slightly different locations for each application, then string searches might be a pain.

Unpivot to the rescue!

select * from (
  select id app_id, alias, name -- key facts
      -- all the substitution stringz!
      ,substitution_string_01, substitution_value_01
      ,substitution_string_02, substitution_value_02
      ,substitution_string_03, substitution_value_03
      ,substitution_string_04, substitution_value_04
      ,substitution_string_05, substitution_value_05
      ,substitution_string_06, substitution_value_06
      ,substitution_string_07, substitution_value_07
      ,substitution_string_08, substitution_value_08
      ,substitution_string_09, substitution_value_09
      ,substitution_string_10, substitution_value_10
      ,substitution_string_11, substitution_value_11
      ,substitution_string_12, substitution_value_12
      ,substitution_string_13, substitution_value_13
      ,substitution_string_14, substitution_value_14
      ,substitution_string_15, substitution_value_15
      ,substitution_string_16, substitution_value_16
      ,substitution_string_17, substitution_value_17
      ,substitution_string_18, substitution_value_18
      ,substitution_string_19, substitution_value_19
      ,substitution_string_20, substitution_value_20
  from apex_190200.WWV_FLOWS -- direct from underlying view
)
unpivot (
(str, val) -- new columns
for rec in -- denoted by 
  ((substitution_string_01, substitution_value_01) as '01'
  ,(substitution_string_02, substitution_value_02) as '02'
  ,(substitution_string_03, substitution_value_03) as '03'
  ,(substitution_string_04, substitution_value_04) as '04'
  ,(substitution_string_05, substitution_value_05) as '05'
  ,(substitution_string_06, substitution_value_06) as '06'
  ,(substitution_string_07, substitution_value_07) as '07'
  ,(substitution_string_08, substitution_value_08) as '08'
  ,(substitution_string_09, substitution_value_09) as '09'
  ,(substitution_string_10, substitution_value_10) as '10'
  ,(substitution_string_11, substitution_value_11) as '11'
  ,(substitution_string_12, substitution_value_12) as '12'
  ,(substitution_string_13, substitution_value_13) as '13'
  ,(substitution_string_14, substitution_value_14) as '14'
  ,(substitution_string_15, substitution_value_15) as '15'
  ,(substitution_string_16, substitution_value_16) as '16'
  ,(substitution_string_17, substitution_value_17) as '17'
  ,(substitution_string_18, substitution_value_18) as '18'
  ,(substitution_string_19, substitution_value_19) as '19'
  ,(substitution_string_20, substitution_value_20) as '20'
  )
)
order by app_id, str 

This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.

select * from apx_app_sub_strings
where val like 'APP_DATE%';

Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Update: As the community quickly pointed out, I missed & forgot about a view already dedicated for such a task. I was too busy looking for a column number, when really I should have used APEX_APPLICATION_SUBSTITUTIONS.

I thought I'd see how 'they' solved the problem, and I was a little surprised to see a bunch of UNION statements.

SELECT ...
           f.substitution_string_18,
           f.substitution_value_18,
           f.substitution_string_19,
           f.substitution_value_19,
           f.substitution_string_20,
           f.substitution_value_20
      from wwv_flow_authorized auth,
           wwv_flows f
     where f.id = auth.application_id )
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_01 as substitution_string,
       substitution_value_01  as substitution_value
  from substitution
where substitution_string_01 is not null
union all
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_02 as substitution_string,
       substitution_value_02  as substitution_value
  from substitution
where substitution_string_02 is not null
union all...

After seeing this I couldn't help but run a brute for comparison, running both solutions x times.

iterations:5000
16.55 secs (.003310 secs per iteration) -- union all
 6.54 secs (.001308 secs per iteration) -- unpivot

I made sure the underlying join was the same, and I'm not all that surprised the unpivot did the job quicker.

Update 2: It appears 20.1 has gone with unpivot.

Transposing data using UNPIVOT

A couple of years ago I posted a method to remove nulls from a report using the Value Attribute Paris - Column template.

Here's an example of how we might utilise the region, within the breadcrumb region position.

Note - some values may have been adjusted from this screenshot for their protection.

Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or on load of the page

Cool idea, can be improved.

And when the JavaScript was placed on one line, it seems so innocuous. It works, so what? What's the harm?
$('dd.t-AVPList-value').each(function(){if ($(this).text().indexOf('~')>0) $(this).hide().prev().hide()})

The trouble with my original method is that it's executing jQuery after the page is rendered. This represents extra work that could be eliminated. The same justification was present in Oracle Forms, as Post-Query triggers were not preferred.

And of course the applies to DML being applied to the database. Sure, you may need some conditional processing and apply a zillion single updates, or you could write some elegant SQL to do it within one update.

The other problem is that the Universal Theme responds to this change with some content movement that can frustrate the user - the body bubbles up to meet the removed content.

New and Improved Solution

Side-note: how can it be new and improved?

In this case I've been talking about columns from a relative simple query, as simple as selecting from scott.emp.

If we can transpose these results, like a magic wand you can do in Excel, then we could just use the Value Attribute Pairs - Row version of the template, and not worry about any jQuery that manipulates the page after it's rendered.

We tranpose columns by surrounding the existing query with a simple UNPIVOT.

select * from (
    select to_char(empno) empno
     ,ename
     ,job
     ,to_char(sal) sal
     ,to_char(comm) comm
    from scott.emp
    where empno = 7788
    --where empno = 7654
) 
unpivot 
(val
 for name in (
    (empno) as 'Emp No'
   ,(ename) as 'Name'
   ,(job) as 'Job'
   ,(sal) as 'Sal'
   ,(comm) as 'Commission'
 )
);

NAME       VAL                                     
---------- --------
Emp No     7788                                    
Name       SCOTT                                   
Job        ANALYST                                
Sal        3000   

4 rows selected.

... query executed with other empno

NAME       VAL                                     
---------- ---------
Emp No     7654                                    
Name       MARTIN                                  
Job        SALESMAN                                
Sal        1250                                    
Commission 1400

5 rows selected.

Notice the result using an emp with a commission shows more rows. The page will only render the data supplied so there no dynamic action required.

Default ordering seems to honour the order of elements in the FOR expression.
Datatypes of columns must match up, hence the to_char around the numeric columns.

Update - I saw Mike on the forums suggest that "transpose" implies a matrix, and offered a combined unpivot/pivot.

tl;dr steps

• surround the existing query with unpivot
• add any datatype conversions necessary to make columns the same
• change report region from pairs with column to pairs with row
• remove declarative column ordering
• remove the dynamic action that finds the tildes

If you want to modify how something presents itself on an APEX page, there are other options to explore before jQuery

• Template options
• Conditional SQL, manifesting as HTML expression in a column
• CSS solutions trump JavaScript.

Simplify, man.

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations

SELECT 'SCOTT' login_id 
  ,'X' alpha, 'Y' beta
  ,10 catgy1
  ,20 catgy2
  ,30 catgy3
  ,40 catgy4
  ,50 catgy5
  ,60 catgy6
FROM dual
/

LOGIN A B     CATGY1     CATGY2     CATGY3     CATGY4     CATGY5     CATGY6
----- - - ---------- ---------- ---------- ---------- ---------- ----------
SCOTT X Y         10         20         30         40         50         60

 1 row selected

Trouble is, I needed the categories described as rows, not columns. So I wrapped the original query within an unpivot, commenting how the syntax represents the translation.

select login_id, alpha, beta, catgy, quota -- new columns
from ( -- existing query
  select 'SCOTT' login_id 
      ,'X' alpha, 'Y' beta      
      ,10 catgy1
      ,20 catgy2
      ,30 catgy3
      ,40 catgy4
      ,50 catgy5
      ,60 catgy6
  from dual
) -- end existing query
unpivot
(
quota -- new column: value
  for catgy in -- new column translating previously separate columns to discrete data
    ( -- and the column -> data translation listed here
     catgy1 as 'CATGY1' 
    ,catgy2 as 'CATGY2'
    ,catgy3 as 'CATGY3'
    ,catgy4 as 'CATGY4'
    ,catgy5 as 'CATGY5' 
    ,catgy6 as 'CATGY6'
    )
)
/

LOGIN A B CATGY       QUOTA
----- - - ------ ----------
SCOTT X Y CATGY1         10
SCOTT X Y CATGY2         20
SCOTT X Y CATGY3         30
SCOTT X Y CATGY4         40
SCOTT X Y CATGY5         50
SCOTT X Y CATGY6         60

 6 rows selected 

Relatively easy! Hope it helps one day.

You can see the results from these statements at livesql.oracle.com

Exploring dynamic pivot options

In looking for information on pivoting variable number of columns, I stumbled across a question I once managed to AskTom, many moons ago.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547

Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
http://www.oracle-developer.net/display.php?id=506

Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238

Then I found this gem, courtesy of Anton Scheffer, via Lucas Jellema
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

The link to Anton's blog post no longer works, but the relevant sql zip is still available in the resources.

It accepts queries like this, which solves my current problem:

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno, job' ) )

   DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
        30                     1          1                     4
        20          2          2          1                      
        10                     1          1          1           

What an awesome solution.

For anyone receiving this error:
ORA-29913: error in executing ODCITABLEDESCRIBE callout
check you have direct SELECT privilege on table, similar to resolving PLS-201 error.

Now to work it into an APEX region, which seems like will be a little wild when applying PPR on report with generic number of columns.

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.

Problem

I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.

create table aus_attractions(id  number, city varchar2(50)
  , attraction1 varchar2(50)
  , attraction2 varchar2(50)
  , reason1 varchar2(50)
  , reason2 varchar2(50)
  );
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand');
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic');
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds');

select id, city
      ,attraction1,attraction2
      ,reason1, reason2
from aus_attractions;

Not optimal relational data design

I'd like to see each attraction by row - 6 rows instead of 3.

You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.

Solution

Then we can turn our original statement into an inline view, serving the unpivot function.

select id, city, attraction, reason, rec_nbr
from ( -- original query:
     (select id, city
         ,attraction1,attraction2
         ,reason1, reason2
   from aus_attractions
   )
 unpivot               -- the magic operator
 ((attraction, reason) -- names of replacement columns
  for rec_nbr in (     -- new column defining data source in literal alias below
   -- split each group of fields in here
   (attraction1, reason1) as 'REC1' 
  ,(attraction2, reason2) as 'REC2'
  )
 )
);

ID      CITY       ATTRACTION        REASON       REC_NBR
------  ---------- ----------------- ------------ -------
1       Perth      weather           sunny        REC1
1       Perth      beaches           white sand   REC2
2       Sydney     bridge            climb        REC1
2       Sydney     blue mountains    scenic       REC2
3       Melbourne  culture           activities   REC1
3       Melbourne  aussie rules      crowds       REC2

6 rows selected

Awesome.

Unpivoted data

Simple, once you've done it the first time...

Documentation

Oracle SQL Language Reference
Oracle Data Warehousing Guide - SQL for Analysis and Reporting

Other great examples of varying depth

OTN - Arup Nanda
Oracle-Base - Tim Hall
Oracle FAQ - Unpivot
Oracle-developer.net - Adrian Billington
AMIS - Lucas Jellema
SQL Snippets: Columns to Rows - UNPIVOT (11g)

If you're not already using the above sites for good reference material, you're missing out.

Also check out this example demonstrated at live.oracle.com

Highlight cell background in APEX report

It seems a very common question in APEX is how do you highlight the background of cells within reports?

There is a heap of information out there, and some of it is becoming dated so I thought I'd offer what seems to be an elegant solution - particularly when you're not targeting a specific column.

We can thank Tyler Muth for an APEX 3.x compatible solution that highlights the text within the column. He described how to use the HTML Expression column attribute - I still use this frequently today.

There are a few solutions that use report templates, but they can be a little fiddly and I don't find them as flexible.

Jari Laine brings us into the APEX 4.x world using jQuery within a dynamic action.

Then I found this stackoverflow post by Tom Petrus (who has also assisted me with jQuery on the OTN forums), which I thought I could adapt for my purposes.

Since I wanted to highlight cells with certain values regardless of column/row, I defined a classic report with a static ID of 'pivot' with the PIVOT sql found in this post.

I added this to the "Execute when Page Loads" page attribute - it could utilised within a dynamic action, if required.

$("#report_pivot tbody tr td").each(function(){
   if ($(this).attr('headers') != 'TOT') {
     if (parseInt($(this).text()) < 1000)
        $(this).css({"background-color":"SandyBrown"});
     else if(parseInt($(this).text()) > 2000)
        $(this).css({"background-color":"lightgreen"});
   }
});

I wanted to ignore the 'Total' column, so I found the first IF statement satisfies that easier than a CSS exclusion clause.
This block could be adapted to do all sorts of things - for instance, I've cleared out the cells in other reports where the value equals 100.

I also added this as inline CSS just to border all the report cells.

table.uReportStandard>tbody>tr>td {
  border: 1px solid #ddd;
}

Here is a screenshot of the final output.

Classic Report with cell highlighting

Run the demo to see it in action. Note all under 1000 are orange, and above 2000 are green. This is done after the page is generated.

Analytic functions within Pivot statements

Today I found that SQL analytics go hand in hand with PIVOT statements.

I had percentage data pivoted by a certain column, but I needed to sort it by
the total across each row.

For the purpose of this demo I create a table with some random monthly data

create table my_data as 
select /*+ no_merge */ username, round(dbms_random.value(1,12)) mth, round(dbms_random.value(1,100),0) val 
from my_users connect by level < 4;

Now with the following query I transpose the monthly totals across as columns using the PIVOT.

select * from (
  select  sum(val) av, username, mth
     ,sum(sum(val)) over (partition by username order by null) tot
  from my_data
  group by username, mth
) 
pivot 
(avg(av) -- avg() because I didn't need to modify the value any further
         -- I'm open to other suggestions
  for mth in 
  (1  as jan
  ,2  as feb
  ,3  as mar
  ,4  as apr
  ,5  as may
  ,6  as jun
  ,7  as jul
  ,8  as aug
  ,9  as sep
  ,10 as oct
  ,11 as nov
  ,12 as dec
  )
)
order by tot desc

Note the highlighted line - an analytical statement that sums yearly total for each username. This was the simplest way I could determine
a) a way to determine that total
b) order the report by the user with the highest totals

I haven't used PIVOT queries too often yet, but I can see how analytical functions will be vital to providing, er, pivotal information.

Oracle PIVOT

A common requirement for queries is to turn rows into columns, or the other way around.

In Excel, we can do this using TRANSPOSE, a bit of patience & know-how, and ctrl+shift+enter.

In Oracle, if we have aggregate data displaying months by row, the old way was to use a bunch of DECODEs (or similar)

SELECT t.name
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jan',1,0)) jan
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'feb',1,0)) feb
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'mar',1,0)) mar
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'apr',1,0)) apr
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'may',1,0)) may
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jun',1,0)) jun
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jul',1,0)) jul
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'aug',1,0)) aug
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'sep',1,0)) sep
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'oct',1,0)) oct
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'nov',1,0)) nov
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'dec',1,0)) dec
FROM events e, bookings b, resources r, resource_types t
WHERE e.event_no = b.event_no
AND r.code = b.resource_code
AND r.type_code = t.code
GROUP BY t.name;

NAME                 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-------------------- --- --- --- --- --- --- --- --- --- --- --- ---
Catering               0   0   2   1   0   0   0   0   0   0   0   0 
Stationary             0   0   1   1   0   0   0   0   0   0   0   0 
Video equipment        0   0   1   1   1   0   0   0   1   0   0   0 
Audio equipment        0   0   0   0   0   0   0   0   0   1   0   0 
Computer equipment     0   0   1   0   0   0   0   0   0   0   0   0 
Locations              0   0   2   2   2   1   1   1   1   1   0   0 

6 rows selected 

Oracle 11g introduced pivot queries.

SELECT * FROM
 ( SELECT COUNT(*) c, t.name, TO_CHAR(start_date,'mon') mth
   FROM events e, bookings b, resources r, resource_types t
   WHERE e.event_no = b.event_no
   AND r.code = b.resource_code
   AND r.type_code = t.code
   GROUP BY t.name, to_char(start_date,'mon')
)
PIVOT
 (SUM(c) -- Add up all my counts
  FOR mth -- Transposing the months
    IN ('jan' as jan
             ,'feb','mar','apr','may','jun'
       ,'jul','aug','sep','oct','nov','dec')
);

NAME                 JAN 'feb' 'mar' 'apr' 'may' 'jun' 'jul' 'aug' 'sep' 'oct' 'nov' 'dec'
-------------------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Catering                           2     1                                                 
Stationary                         1     1                                                 
Video equipment                    1     1     1                       1                   
Audio equipment                                                              1             
Computer equipment                 1                                                       
Locations                          2     2     2     1     1     1     1     1             

6 rows selected 

Note line 12 where we can provide column aliases to the fresh output.

As with most esoteric SQL functions, there are quite a few good examples on the web that I'm not out to emulate - the prime purpose of this post was to help remind me what's going on.
That being said, Tim Hall and Arup Nanda have the most concise articles.

I particularly like Lucas Jellema's example linked by Arup using

select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)
/

I wonder if that might be an interesting catalyst in some future queries
Update August 2014 - yes it did! -> Unpivot

Friday funny - I'll add it later

Spotted in the 11.1 Oracle documentation ...

Much improved in 11.2 :-)

Ever found something similar in your own code?