Terrific Oracle forum exemplified by "wi (dd.mm.yyyy-dd.mm.yyyy)"

Did you ever had any problems with Oracle based issues like Database, APEX, Forms, SQL and so on? Of course you had.
Normally the Oracle Metalink is the first address for everybody who has a contract with Oracle. But in some cases the Oracle forum help is unbeatable.
What I mean is. Whenever you have a problem with SQL commands or APEX issues the fastest way to find a solution is to ask in the Oracle forums.

I want to bring up a special example I had. I needed some week and date values via a sql command for a month like that:
wi (dd.mm.yyyy-dd.mm.yyyy)
05 (01.02.2009-01.09.2009)
06 (02.02.2009-08.09.2009)
07 (09.02.2009-15.09.2009)
08 (16.02.2009-22.09.2009)
09 (23.02.2009-28.09.2009)

I couldn't fix it myself so I wrote a forum entry:
http://forums.oracle.com/forums/message.jspa?messageID=2842808
I got more then a tip I got a solution for the problem.

A couple of weeks later I just found out that the last week in February was missing. So I wrote a new post which was answered just a couple of hours later.
http://forums.oracle.com/forums/message.jspa?messageID=3279392

I think this type of help is sometimes more valuable then any GOLD support you can get. :D

By the way here is the solution for the problem: ;D

-- select all weeks for a year
select to_char(dt+(7*rn),'WW') ||' ('||
       to_char(dt+(decode(rn,0,0,(7*rn)+1-offset))) ||'-'||
       case when dt+(7*(rn+1))-offset > last_day(dt) then
                   last_day(dt) ||')'
              else
                   dt+(7*(rn+1))-offset ||')'
              end as show_value,
       to_char(dt+(7*rn),'WW') as return_value
  from (select to_date(mes||'2009','MMYYYY') as dt, to_number(to_char(to_date(mes||'2009','MMYYYY'),'D')) as offset
         from dual, (select to_char(rownum, '00') mes from dual connect by rownum <= 12))
       ,(select rownum-1 rn from dual connect by rownum <= 6) 
where trunc(to_date(dt+(decode(rn,0,0,(7*rn)+1-offset))), 'MM') = dt
order by dt, return_value asc
-- select all week for a month
select to_char(dt+(7*rn),'IW') ||' ('||
       to_char(dt+(decode(rn,0,0,(7*rn)+1-offset))) ||'-'||
       case when dt+(7*(rn+1))-offset > last_day(dt) then
                   last_day(dt) ||')'
              else
                   dt+(7*(rn+1))-offset ||')'
              end as show_value,
       to_char(dt+(7*rn),'IW') as return_value
  from (select to_date(02||2009,'MMYYYY') as dt from dual)
       ,(select to_number(to_char(to_date(02||2009,'MMYYYY'),'D')) as offset from dual)
      ,(select rownum-1 rn from dual connect by rownum <= 5) 
  where trunc(to_date(dt+(decode(rn,0,0,(7*rn)+1-offset))), 'MM') = dt
-- the way I use it in APEX
select to_char(dt+(7*rn),'IW') ||' ('||
       to_char(dt+(decode(rn,0,0,(7*rn)+1-offset))) ||'-'||
       case when dt+(7*(rn+1))-offset > last_day(dt) then
                   last_day(dt) ||')'
              else
                   dt+(7*(rn+1))-offset ||')'
              end as show_value,
       to_char(dt+(7*rn),'IW') as return_value
  from (select to_date(:P1_MONTH||:P1_YEAR,'MMYYYY') as dt from dual)
       ,(select to_number(to_char(to_date(:P1_MONTH||:P1_YEAR,'MMYYYY'),'D')) as offset from dual)
      ,(select rownum-1 rn from dual connect by rownum <= 5) 
  where trunc(to_date(dt+(decode(rn,0,0,(7*rn)+1-offset))), 'MM') = dt

Special thanks to BluShadow and Miguel Chillitupa!

Post Tags:

Personal stuff