Ho we can use "with t as" in reports query [message #656787] |
Wed, 19 October 2016 02:11 |
shahzad-ul-hasan
Messages: 625 Registered: August 2002
|
Senior Member |
|
|
with t as (
select add_months(to_date('01/01/&year','mm/dd/yyyy'),level - 1) dt
from dual
connect by level <= 12
)
select to_char(dt,'fmMonth, yyyy') dt,
trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 6 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),6) -
least(dt - trunc(dt,'iw'),6) work_days
from t
/
i have above query code . how i can use this in my report builder main query. i am using reports 6i.
|
|
|
Re: Ho we can use "with t as" in reports query [message #656788 is a reply to message #656787] |
Wed, 19 October 2016 02:16 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
select to_char(dt,'fmMonth, yyyy') dt,
trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 6 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),6) -
least(dt - trunc(dt,'iw'),6) work_days
from (
select add_months(to_date('01/01/&year','mm/dd/yyyy'),level - 1) dt
from dual
connect by level <= 12
)
|
|
|
Re: Ho we can use "with t as" in reports query [message #656790 is a reply to message #656787] |
Wed, 19 October 2016 02:53 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The subquery factoring capability was introduced in release 9.2 I think, so you wont have access to it in your old 6i client. If you really want to use it, perhaps you could create a view with the clause and use that in your query?
|
|
|
|