Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

does an inline view execute before the rest of the select?

Ken QuiriciNov 13 2005 — edited Nov 14 2005
Hi,

The O'Reilly book Mastering Oracle Sql defines an inline view simply as a
subquery in a select FROM clause. This conforms to the definitions in
the Oracle 9i and 10g docs.

But unlike the Oracle docs, the O'Reilly book says the inline view is,
like a WITH clause, executed before the rest of the query. I can't see
how this can be done unless the inline view makes no reference to
other tables joined in the FROM clause.

Is there a species of inline view in 9i/10g that IS restricted as above -
no refs to other tables in the FROM clause - and what is it called?

Thanks for any help.

Ken Quirici

Comments

SmithJohn45

can you give some " sample data " like this:

with t as (Select 'account01' as account_name, '1990' as year, 'Jan' as month from dual
           Select 'account02' as account_name, '2000' as year, 'Feb' as month from dual
)
select ( your query here ) from t;

the seniors can help after checking the actual results of your query will return.
Help seniors to help yourself...

Stax 
SQL> ed
Wrote file afiedt.buf


  1  with t (account_name,year,month,sal) as (
  2   select 'u1','2022','January',150 from dual union all
  3   select 'u1','2022','February',200 from dual union all
  4   select 'u1','2022','March',300 from dual
  5  )
  6  select
  7    t.*
  8   ,sum(sal) over (partition by account_name order by  to_date(month||year,'MonthYYYY','NLS_DATE_LANGUAGE=AMERICAN')) ssal
  9* from t
SQL> /


AC YEAR MONTH           SAL       SSAL
-- ---- -------- ---------- ----------
u1 2022 January         150        150
u1 2022 February        200        350
u1 2022 March           300        650
1 person found this helpful
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 12 2005
Added on Nov 13 2005
14 comments
2,686 views