Oracle APEX Interactive report date order by

Interactive report date order by

 

DECODE over CASE statement

Oracle APEX - IR sort order not working?

We all know and love Interactive reports in APEX. This is a quick post showing a typical user case where sort order was rude to a customer.  

Why?

Looking at the source code for this region nothing jumps out:

SELECT           
     event_id,        
     DECODE (evf.start_date_did,
             0, null,
             evdat.calendar_date)
     AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did   

For some reason APEX was seeing this date as a varchar. But again why would this not work if column returned is defined as date in a table.

Digging deeper into a problem we looked at definition of DECODE function and noticed this: 

"..If the first result is NULL, then the return value is converted to VARCHAR2."

Great this as usual confirms that we have an issue in the query not in APEX itself. 

Workarounds: 
1. Rewrite your query to use a date over a NULL in your decode statement

SELECT           
     event_id,        
     DECODE (evf.start_date_did,
             0, to_date('01-JAN-1900', 'dd-mon-yyyy'),
             evdat.calendar_date)
     AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did

Or even better use CASE statement: 

SELECT           
     event_id,        
     CASE
      WHEN evf.start_date_did != 0
       THEN evdat.calendar_date END              AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did  

Summary, there is a difference between DECODE and CASE statement working with NULLS which can cause similar issues so be warned and keep an eye out. 

Happy APEXing,
Lino

Oracle APEX - Show hide regions and items on large scale

Show hide regions and items on large scale

 

Using DA or JavaScript?

Oracle APEX - handling hide and show methods with a catch

Simple problem - Page with large number of items (527+ form fields for example :D) and depending on certain field you want to show some where the rest stay hidden.  

Of course you could do this declarative way by using Dynamic actions where for you first hide all then for certain condition you show items. Only concern the more conditions you put in things become cumbersome.

But issues that I came across came from the fact that my approach was for that reason different. Why? Because I was dealing with 527 items on the form where there were 37 different groups of items dictated by 1 form field so was looking into a way how to process most of show/hide behavior with less code. 

The example of code above is a demo one not the original form but concept and the problem stays the same. Lets say APEX_APP_ID contains 37 values where the rest columns 400+ of them fit in one of groups (sometimes in more than one so could not just group them easily in regions). Hopefully this describes the problem well. I used regions to group most of items together but still challenge was there. 

Task number 1. Hide all

Great way of doing this is by using CSS Classes attribute under your page item/region properties.

 Then all it comes down to is running once all elements have their class set:

$( ".my_hide_all").hide();

Awesome - all items are now hidden. So simply have to show them when I want. Easy right? Well I thought so too before learning this lesson. 

Task 2. Show items
I thought this should do the trick:

apex.item( "P1_ITEM" ).show();

As you can image it did not work. The reason is because my class was set on a wrong level;  so DOME row CSS still had display:none;  set.

In my above example solution was to use: 

 $('#P1_ITEM').closest(".my_hide_all").show();

This then worked fine. I admit did not see this coming. 

Once I placed a CSS class on 

appearance section CSS Classes things were back as expected and I was able to show it using

apex.item( "P1_ITEM" ).show();

On the other side if I used DA to first hide then to show an item (with True Action-> Action Show) worked like a char straight away. Well I guess lesson learned for today. :D

Happy APEXing,
Lino