Wednesday, 20 March 2013

Playing with dates, again

When creating LOVs for APEX I sometimes debate to myself whether to make a static or dynamic LOV.

I had one scenario where having some SQL was handy, so I started with this
SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')
FROM dual
CONNECT BY LEVEL <= 7;
It's possible to then place this as an inline view within a subquery factoring clause, to use fancy terminology. This makes it easy to share column data.
WITH data AS 
 (SELECT NEXT_DAY(sysdate, 'MON')+ROWNUM-1 dt
  FROM dual
  CONNECT BY LEVEL <= 7)
SELECT TO_CHAR(dt,'DY'), TO_CHAR(dt,'Day')
FROM data;

TO_CHAR(DT,'DY') TO_CHAR(DT,'DAY')
---------------- -----------------
MON              Monday            
TUE              Tuesday           
WED              Wednesday         
THU              Thursday          
FRI              Friday            
SAT              Saturday          
SUN              Sunday            

 7 rows selected 
Simple, but effective.

Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?
Post a Comment