Pfthb

I am trying to achieve something like this:

I have two large tables and there is no common key between them. Obviously when I want to join them, it is a Cartesian product like m x n rows. I want to avoid that. I will read one record from tableA and then match it with tableB all the rows but at least I will have control over how many records I have processed in tableA.

Currently, I am not knowing how far the query has executed.

My question is can I do that in WITH statement:

I have taken a simple case say EMP table from SCOTT/TIGER example and one record at a time would match with DEPT table and return DNAME. I have written this query but is not doing as expected. And how to reference DNAME from 2nd query:

with emp_rec as (select * from emp),
 dept_rec as (select dname 
 from dept, emp_rec
 where dept.deptno in (10, 20) and 
 dept.deptno = emp_rec.deptno)
select * 
from emp_rec

Results which I am expecting is similar to joining EMP and DEPT tables on emp/deptno = dept/deptno. But I want to do it one EMP record at a time without CURSOR or STORED PROCEDURE in pure SQL. Hope I was able to explain what is on my mind.

What is that I am doing wrong, please help.
thank you

Hi,

I have been able to solve my problem. Instead of SELECTING from EMP_REC I changed to DEPT_REC, it worked. Here is the new query:

with emp_rec as (select * from emp),
 dept_rec as (select emp_rec.*, dname 
 from dept, emp_rec
 where dept.deptno in (10, 20) and 
 dept.deptno = emp_rec.deptno)
select * 
from dept_rec

thanks

I am able to control both queries separately. And the restriction, that subQueries do not refer to parentQuery's variables, is gone.

WITH statement provides multiple queries to interact with one another. This is different from subqueries in FROM clause where each query is independent of other.

Another feature of CTE which became useful here, the final query can call any of the subqueries in WITH clause. I had two subqueries in WITH clause EMP_REC and DEPT_REC. I called DEPT_REC and it worked.

To understand the data in this query please use Oracle's SCOTT/TIGER database which has standard tables EMP (14 rows), DEPT etc.

thanks for bearing with me
bb23850