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