OraFAQ Forum: Reports & Discoverer » Ho we can use "with t as" in reports query

Home » Developer & Programmer » Reports & Discoverer » Ho we can use "with t as" in reports query (Reports 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ho we can use "with t as" in reports query [message #656787]

Wed, 19 October 2016 02:11

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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
          )

Report message to a moderator

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: 8978
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?

Report message to a moderator

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

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

Report message to a moderator

Previous Topic:	Format Mask problem
Next Topic:	Reports 6i ref_cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 19:38:48 CDT 2025