CODEWITHSUNDEEP
oracle-databasevieworacle11gsubquery-factoring
sameer
sameer

Reputation: 220

Subquery factoring: Inner Join with each other

I have four WITH clauses. I wanted to know if it is possible to use inner joins among them.

I searched on the net and i could not find anything related to this.

Oracle version: 11g

** EDIT **

WITH
    GETDATABYDAY AS
    (
        select column1, column2
        from table1 
        where sales > 2000
     )
    SELECT
      column1,
      column2
    FROM
      GETDATABYDAY;

WITH
    GETDATABYDAY1 AS
    (
        select column3, column2
        from table1 
        where date between 'date1' and 'date2'
    )
    SELECT
        column3,
        column2
    FROM
        GETDATABYDAY1;

Assume that there are two more WITH named: GETDATABYDAY2 and GETDATABYDAY3

Is it possible to use inner join on all GETDATABYDAY, GETDATABYDAY1, GETDATABYDAY2 and GETDATABYDAY3?

Upvotes: 0

Views: 1009

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16915

I don't think it can work, since every with clause is part of a whole different query.

You can create db views though, and use them instead of the with clause.

Upvotes: 0

user330315
user330315

Reputation:

Something like this will work:

with first_cte as ( 
    select ...
    from ...
), second_cte as (
    select ...
    from first_cte
      join some_table on ...
), third_cte as (
    select ...
    from ...
) fourth_cte as (
    select ...
    from some_other_table
      join second_cte on ...
) 
select ..
from fourth_cte
   join third_cte on ....

Upvotes: 2

Related Questions