Home » Developer & Programmer » Reports & Discoverer » Ho we can use "with t as" in reports query (Reports 6i)
Ho we can use "with t as" in reports query [message #656787] Wed, 19 October 2016 02:11 Go to next message
shahzad-ul-hasan
Messages: 561
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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 Go to previous messageGo to next message
John Watson
Messages: 8344
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?
Re: Ho we can use "with t as" in reports query [message #656791 is a reply to message #656790] Wed, 19 October 2016 03:21 Go to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
OK
Previous Topic: Format Mask problem
Next Topic: Reports 6i ref_cursor
Goto Forum:
  


Current Time: Sun Sep 20 07:12:35 CDT 2020