Fun with OBIEE SQL - Part I

I suspect this will be the first of many in the series.

I was grabbing physical SQL for some tests and noticed this little gem.

I have prettified it for your reading pleasure.

WITH 
SAWITH0 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH1 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) ),
SAWITH2 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH3 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH4 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH5 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH6 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH7 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH8 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH9 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH10 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH11 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
),
SAWITH12 AS 
(
  select T351236.TIME_ID as c2
  from TIME T351236
  where  ( T351236.DAY = TO_DATE('2010-04-14' , 'YYYY-MM-DD') ) 
)
(
  (
    (
      (
        (
          (
            (
              (
                (
                  (
                    (
                      (
                        select distinct '201002' as c1
                        from SAWITH0
                        union
                        select distinct '201003' as c1
                        from SAWITH1
                      )
                      union
                      select distinct '201004' as c1
                      from SAWITH2
                    )
                    union
                    select distinct '201005' as c1
                    from SAWITH3
                  )
                  union
                  select distinct '201006' as c1
                  from SAWITH4
                )
                union
                select distinct '201007' as c1
                from SAWITH5
              )
              union
              select distinct '201008' as c1
              from SAWITH6
            )
            union
            select distinct '201009' as c1
            from SAWITH7
          )
          union
          select distinct '201010' as c1
          from SAWITH8
        )
        union
        select distinct '201011' as c1
        from SAWITH9
      )
      union
      select distinct '201012' as c1
      from SAWITH10
    )
    union
    select distinct '201013' as c1
    from SAWITH11
  )
  union
  select distinct '201014' as c1
  from SAWITH12
)

First off, I had never thought to use the WITH clause in such a way. Note there are 13 WITH statements above.

Second, WTF is up with all those UNIONs?

Third (and this isn't OBI EE's fault), what's up with the DISTINCT clause?

All the WITH clauses are doing is getting the TIME_ID (in other words, a single record) for the current day...and doing that 13 times to get 13 records. If this had been against Oracle originally, I probably would have used DUAL to generate the 13 rows. Since those values are periods (weeks actually), you have your TIME dimension to work with...so maybe something like this:

SELECT DISTINCT week
FROM time
WHERE day <= SYSDATE
  AND day >= SYSDATE - ( 13 /*weeks*/ * 7 )
ORDER BY week DESC;

Looks like some low hanging fruit to me.