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?

1 comment:

Anonymous said...

static lov means we are not passing any value for filtering condition i.e, bind variables.



I case of dynamic the result is based on some other items like cascading lov in apex 4.1 above.