Hi tom,

 can you please explain.....

  1   select e1.*, e2.ename from emp e1,dept d ,emp e2
  2   where e1.deptno = d.deptno(+)
  3*  and e1.mgr = e2.empno(+)
  4  /

EMPNO ENAME    JOB  MGR HIREDATE     SAL   COMM DEPTNO ENAME
---- -------- ---- ---- ---------    ---    ---- ------ ----
7369 SMITH   CLERK   7902  17-DEC-80 800          20  FORD 
7499 ALLEN  SALESMAN 7698  20-FEB-81 1600  300    30  BLAKE
7521 WARD   SALESMAN 7698  22-FEB-81 1250  500    30  BLAKE
7566 JONES  MANAGER  7839  02-APR-81 2975         20  KING
7654 MARTIN SALESMAN 7698  28-SEP-81 1250 1400    30 BLAKE
7698 BLAKE  MANAGER  7839  01-MAY-81  850         30 KING
7782 CLARK  MANAGER  7839  09-JUN-81  450         10 KING
7788 SCOTT  ANALYST  7566  19-APR-87 3000         20 JONES
7839 KING  PRESIDENT       17-NOV-81 5000         10
7844 TURNER SALESMAN 7698 08-SEP-81  1500    0    30 BLAKE
7876 ADAMS  CLERK    7788 23-MAY-87  1100         20 SCOTT
7900 JAMES  CLERK    7698 03-DEC-81   950         30 BLAKE
7902 FORD   ANALYST  7566 03-DEC-81  3000         20 JONES
7934 MILLER CLERK    7782 23-JAN-82  1300         10 CLARK
10   kobi   Player        02-OCT-02  5000     0

15 rows selected.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

How come one table emp from scott/tiger can be outer joined to two tables emp(self joined) and dept ?

Thanks, 

I know this is not outer-join related, but the discussion of predicates brought a question to my mind.  I have a colleague who uses a varchar2 to store a date value (I know, I know...).

Her code only works when the GT "space" condition is given first.  There are 2 values in the table, space, and a date value

This is 8.1.7.  Does Oracle sometimes enforce the predicate line-by-line?


SQL> select count(*) from ps_tl_rptd_elptime
  2  where to_date(user_field_3,'dd-mon-yyyy') = trunc(sysdate + 4)
  3  and user_field_3 > ' ';
where to_date(user_field_3,'dd-mon-yyyy') = trunc(sysdate + 4)
              *
ERROR at line 2:
ORA-01847: day of month must be between 1 and last day of month


SQL>
SQL> select count(*) from ps_tl_rptd_elptime
  2  where user_field_3 > ' '
  3  and to_date(user_field_3,'dd-mon-yyyy') = trunc(sysdate + 4);

  COUNT(*)
----------
         5

SQL>
SQL>
SQL> select user_field_3,count(*)
  2  from ps_tl_rptd_elptime
  3  group by user_field_3;

USER_FIELD_3      COUNT(*)
--------------- ----------
                        19
01-DEC-2002          18224
01-FEB-2003          11680
01-MAR-2003          12503
02-JUN-2002          19065
03-AUG-2002          11391
03-NOV-2002          18612 

OK, Tom, 
Let me start from the beginning.
I have two tables t1 and t2:
SQL> select * from t1;
INDEX_NAME      COLUMN_NAME      COLUMN_POSITION
--------------- ---------------- ---------------
INDEX2_IN_BASE  INT1                           3
INDEX2_IN_BASE  USER_ID                        1
INDEX_IN_BASE   ACTION_DATE                    2
INDEX_IN_REPORT ACTION_ID                      2
INDEX_IN_REPORT TEST1                          1
INDEX_IN_REPORT TEST2                          3
SQL>select * from t2;
INDEX_NAME      COLUMN_NAME      COLUMN_POSITION
--------------- ---------------- ---------------
INDEX2_IN_BASE  INT2                           1
INDEX2_IN_BASE  INT3                           3
INDEX2_IN_BASE  INT4                           4
INDEX_IN_REPORT ACTION_ID                      2
INDEX_IN_REPORT USER_ID                        1
INDEX2_IN_BASE  ACTION_DATE                    2

I want to list t1.INDEX_NAME, t1.column_name,t2.INDEX_NAME, t2.column_name from t1 and t2 which have the same index_name, but different column_name according to their column_position.

Here is my query and result:
SQL> SELECT  a.INDEX_NAME, a.COLUMN_NAME,j.INDEX_NAME, j.COLUMN_NAME 
  2  FROM (
  3  SELECT INDEX_NAME, COLUMN_NAME,COLUMN_POSITION FROM t1
  4  MINUS 
  5  SELECT  INDEX_NAME, COLUMN_NAME,COLUMN_POSITION FROM t2) a 
  6  FULL OUTER JOIN
  7  (SELECT INDEX_NAME, COLUMN_NAME,COLUMN_POSITION  FROM t2
  8  MINUS
  9  SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM t1) j 
 10  on a.INDEX_NAME = j.INDEX_NAME
 11  and a.COLUMN_POSITION  =  j.COLUMN_POSITION;

INDEX_NAME      COLUMN_NAME      INDEX_NAME      COLUMN_NAME
--------------- ---------------- --------------- -----------
INDEX2_IN_BASE  USER_ID          INDEX2_IN_BASE  INT2
INDEX2_IN_BASE  INT1             INDEX2_IN_BASE  INT3
INDEX_IN_REPORT TEST1            INDEX_IN_REPORT USER_ID
INDEX_IN_REPORT TEST2
INDEX_IN_BASE   ACTION_DATE

I missed COLUMN_NAME=INT4 from t2. This query is the same as Left outer join somehow. Here is my right outer join result:
SQL> SELECT  a.INDEX_NAME, a.COLUMN_NAME,j.INDEX_NAME, j.COLUMN_NAME
  2  FROM (
  3  SELECT INDEX_NAME, COLUMN_NAME,COLUMN_POSITION FROM t1
  4  MINUS 
  5  SELECT  INDEX_NAME, COLUMN_NAME,COLUMN_POSITION FROM t2) a 
  6  RIGHT OUTER JOIN
  7  (SELECT INDEX_NAME, COLUMN_NAME,COLUMN_POSITION  FROM t2
  8  MINUS
  9  SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM t1) j 
 10  on a.INDEX_NAME = j.INDEX_NAME
 11  and a.COLUMN_POSITION  =  j.COLUMN_POSITION;

INDEX_NAME      COLUMN_NAME      INDEX_NAME      COLUMN_NAME
--------------- ---------------- --------------- -----------
INDEX2_IN_BASE  INT1             INDEX2_IN_BASE  INT3
INDEX2_IN_BASE  USER_ID          INDEX2_IN_BASE  INT2
INDEX_IN_REPORT TEST1            INDEX_IN_REPORT USER_ID
                                 INDEX2_IN_BASE  INT4
                                 INDEX2_IN_BASE  ACTION_DATE
I really need a FULL OUTER JOIN query to show the extra COLUNM_NAME from both t1 and t2.
Hope this will help you to understand what I try to do so that you will help me to get the right query.

Thank you very much. 

During using out joins I am facing the following issue which I am unable to understand,

I am using two queries and just chanding e.name to e.empno and the query works but why not working with e.ename.

SQL>  select e.ename, d.dname from emp e full outer join dept d
  2   on (e.deptno=d.deptno);
 on (e.deptno=d.deptno)
                      *
ERROR at line 2:
ORA-00600: internal error code, arguments: [qcscpqbTxt], [904], [], [], [], [], [], []


SQL>  select e.empno, d.dname from emp e full outer join dept d
  2   on (e.deptno=d.deptno);

     EMPNO DNAME
---------- --------------
      7782 ACCOUNTING
      7839 ACCOUNTING
      7934 ACCOUNTING
      7369 RESEARCH
      7876 RESEARCH
      7902 RESEARCH
      7788 RESEARCH
      7566 RESEARCH
      7499 SALES
      7698 SALES
      7654 SALES
      7900 SALES
      7844 SALES
      7521 SALES
           OPERATIONS

15 rows selected.

SQL> 

 

Hi Tom,
Could you help me find out what's a problem on this query:

SQL> select nvl(a.pay_qtr, b.pay_quarter) pay_q,
  2         nvl(a.clm_l,0) clm_l, nvl(a.clm_a,0) clm_a,
  3         nvl(a.clm_t,0) clm_t, nvl(b.clm_h,0) clm_h
  4  from
  5   (select pay_qtr,
  6      sum(decode(flag_death,'Y', decode(plan_type,'L', re_pay_amt,0),0)) clm_l,
  7      sum(decode(flag_death,'Y', decode(plan_type,'R', re_pay_amt,0),0)) clm_a,
  8      sum(decode(flag_death,'N', decode(plan_type,'R', re_pay_amt,0),0)) clm_t
  9    from claim_paid_by_reinsurer
 10    where reinsurer_id = :reinsurer_id and pay_year = :pay_year
 11    group by pay_qtr) a
 12  full outer join
 13   (select pay_quarter, sum(re_pay_amt) clm_h
 14    from claim_h_paid_by_reinsurer
 15    where reinsurer_id = :reinsurer_id and pay_year = :pay_year
 16    group by pay_quarter) b
 17  on (a.pay_qtr = b.pay_quarter) ;
select nvl(a.pay_qtr, b.pay_quarter) pay_q,
*
ERROR at line 1:
ORA-00918: column ambiguously defined

I cannot find any columns that are ambiguous. And also when
I use left outer join instead, it give me then correct answers.

SQL> select nvl(a.pay_qtr, b.pay_quarter) pay_q,
  2         nvl(a.clm_l,0) clm_l, nvl(a.clm_a,0) clm_a,
  3         nvl(a.clm_t,0) clm_t, nvl(b.clm_h,0) clm_h
  4  from
  5   (select pay_qtr,
  6      sum(decode(flag_death,'Y', decode(plan_type,'L', re_pay_amt,0),0)) clm_l,
  7      sum(decode(flag_death,'Y', decode(plan_type,'R', re_pay_amt,0),0)) clm_a,
  8      sum(decode(flag_death,'N', decode(plan_type,'R', re_pay_amt,0),0)) clm_t
  9    from claim_paid_by_reinsurer
 10    where reinsurer_id = :reinsurer_id and pay_year = :pay_year
 11    group by pay_qtr) a
 12  left outer join
 13   (select pay_quarter, sum(re_pay_amt) clm_h
 14    from claim_h_paid_by_reinsurer
 15    where reinsurer_id = :reinsurer_id and pay_year = :pay_year
 16    group by pay_quarter) b
 17  on (a.pay_qtr = b.pay_quarter) ;

P      CLM_L      CLM_A      CLM_T      CLM_H
- ---------- ---------- ---------- ----------
1      50000          0      50000          0

Here is version I used :

SQL> select version from v$instance ;

VERSION
-----------------
9.2.0.1.0

In the query above, the first subquery has one row and the second has no row.
 

I'm afraid that I can't give a simple and small test case for you. claim_paid_by_reinsurer and claim_h_paid_by_reinsurer are some complex views. Did the full outer join have any problem when using on these views?

Anyway, the following is the structure of the view

SQL> desc claim_paid_by_reinsurer
 Name                           Null?    Type
 ---------------------------------------------------
 CLM_DOC_NO                     NOT NULL VARCHAR2(10)
 PLAN_TYPE                      NOT NULL VARCHAR2(1)
 RIDER_CODE                     NOT NULL VARCHAR2(2)
 OCCUR_DATE                              DATE
 CLM_CAUSE                               VARCHAR2(7)
 FLAG_DEATH                              VARCHAR2(1)
 POLICY_ID                               VARCHAR2(7)
 RE_YEAR                                 VARCHAR2(4)
 RE_QUARTER                              VARCHAR2(1)
 RE_COVERAGE_TYPE                        VARCHAR2(2)
 CERT_ID                                 VARCHAR2(6)
 REINSURER_ID                   NOT NULL VARCHAR2(2)
 TOTAL_SUM                               NUMBER(8)
 ER_FLAG                                 VARCHAR2(1)
 PAY_DATE                                DATE
 PAY_AMT                                 NUMBER(10,2)
 PAY_YEAR                                VARCHAR2(4)
 PAY_QTR                                 VARCHAR2(1)
 RE_PAY_AMT                              NUMBER
 PAY_RATE                                NUMBER(5,2)

SQL> desc claim_h_paid_by_reinsurer
 Name                           Null?    Type
 ---------------------------------------------------
 POLICY_ID                      NOT NULL VARCHAR2(7)
 REINSURER_ID                   NOT NULL VARCHAR2(2)
 PAY_DATE                                DATE
 PAY_YEAR                                VARCHAR2(4)
 PAY_QUARTER                             VARCHAR2(1)
 RE_PAY_AMT                              NUMBER

and here are their data :

SQL> select reinsurer_id, pay_year, pay_qtr,
  2    flag_death, plan_type, re_pay_amt
  3  from claim_paid_by_reinsurer ;

RE PAY_ P F P RE_PAY_AMT
-- ---- - - - ----------
02 2547 1 Y L      50000
02 2549 1 N R      50000
02 2549 1 N R      12500
05        Y R      22500
04        Y R      52500
02 2547 1 N R      50000
05 2547 4 Y L      22500
04 2547 4 Y L      52500
05 2547 2 Y L      15000
04 2547 2 Y L      35000
05 2547 2 Y R      15000

RE PAY_ P F P RE_PAY_AMT
-- ---- - - - ----------
04 2547 2 Y R      35000
02 2549 1 Y R      50000
02        Y R      50000

14 rows selected.

SQL> select reinsurer_id, pay_year, pay_quarter,
  2    re_pay_amt
  3  from claim_h_paid_by_reinsurer ;

RE PAY_ P RE_PAY_AMT
-- ---- - ----------
06                 0
02              3250
06             76500

Thank you very much for your helps.
 

This site rocks!  Your efforts are much appreciated.

Due to an encryption project, we have replaced an existing table with a view of that same table only now two of the columns have user defined functions that decrypt as necessary.  Although we are not joining on either of these function based columns but rather a different indexed column, if we include either of the function based 
columns in the select and have an outer join then we see a full table scan. Why? Can this be avoided without removing the join or the function based columns?

Here's a simple reproduction of the issue...

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> CREATE TABLE my_table AS SELECT object_id,object_name FROM all_objects
  2  /

Table created.

SQL> CREATE INDEX my_table_object_id_idx ON my_table(object_id)
  2  /

Index created.

SQL> CREATE INDEX my_table_object_name_idx ON my_table(object_name)
  2  /

Index created.

SQL> CREATE TABLE my_other_table AS SELECT object_id,created FROM all_objects
  2  /

Table created.

SQL> CREATE INDEX my_other_table_created_idx ON my_other_table(created)
  2  /

Index created.

SQL> CREATE OR REPLACE FUNCTION my_upper (p IN VARCHAR2)
  2  RETURN VARCHAR2
  3  IS
  4  BEGIN
  5      RETURN upper(p);
  6  END;
  7  /

Function created.

SQL> CREATE OR REPLACE VIEW my_view AS
  2  SELECT object_id, my_upper(object_name) object_name 
  3  FROM my_table
  4  /

View created.

-- Now query does FTS on my_table
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT v.object_name,t.created
  2    FROM my_view v,
  3        my_other_table t
  4   WHERE t.created = TO_DATE('11/14/2000 8:44:17 PM','MM/DD/YYYY HH:MI:SS AM')
  5     AND t.object_id = v.object_id (+)
  6  /

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'MY_OTHER_TABLE'
   4    3         INDEX (RANGE SCAN) OF 'MY_OTHER_TABLE_CREATED_IDX' (
          NON-UNIQUE)

   5    1     SORT (JOIN)
   6    5       VIEW OF 'MY_VIEW'
   7    6         TABLE ACCESS (FULL) OF 'MY_TABLE'

-- if we remove outer join the index is used as desired
SQL> SELECT v.object_name,t.created
  2    FROM my_view v,
  3        my_other_table t
  4   WHERE t.created = TO_DATE('11/14/2000 8:44:17 PM','MM/DD/YYYY HH:MI:SS AM')
  5     AND t.object_id = v.object_id
  6  /

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'MY_OTHER_TABLE'
   3    2       INDEX (RANGE SCAN) OF 'MY_OTHER_TABLE_CREATED_IDX' (NO
          N-UNIQUE)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'MY_TABLE'
   5    4       INDEX (RANGE SCAN) OF 'MY_TABLE_OBJECT_ID_IDX' (NON-UN
          IQUE)
 

Dear Tom,


I have on question regarding the use of (+) on a column that is not part of the join; I cannot understand the results of this : 

SQL> create table A(x number);

Table created.

SQL> 
SQL> create table B(x number, y number);

Table created.

SQL> 
SQL> insert into A values(1);

1 row created.

SQL> 
SQL> insert into A values(2);

1 row created.

SQL> 
SQL> 
SQL> insert into B values(1, 1);

1 row created.

SQL> 
SQL> insert into B values(2, 2);

1 row created.

SQL> 
SQL> 
SQL> select * from A, B where A.x = b.x (+);

         X          X          Y
---------- ---------- ----------
         1          1          1
         2          2          2

SQL> 
SQL> select * from A, B where A.x = b.x (+) and b.x (+) = 1;

         X          X          Y
---------- ---------- ----------
         1          1          1
         2

SQL> 


I formerly thought that the last query would bring only one row, ie the first one.

How does it happen that the columns of the second table are replaced with NULL when the condition b.x (+) = 1 is not verified ? Is this normal ?

I tought that : b.x (+) = 1 and (b.x = 1 or b.x is null) where equivalent (of course on a not null column).


Best regards, 

Laly. 

view can be used using some logic if possible. 

  1  select translate(code,0123456789,'ABCDEFGHIJKLM')  from
  2* ( select 'CODE'||rownum code  from dict where rownum < 10 )
SQL> /

TRANSLATE(CODE,0123456789,'ABCDEFGHIJKLM')
--------------------------------------------
CODEA
CODEB
CODEC
CODED
CODEE
CODEF
CODEG
CODEH
CODEI

 

SQL> l
  1  select * from  (
  2  select lhs.pk1 lhs_pk1,lhs.pk2 lhs_pk2,rhs.pk1 rhs_pk1,rhs.pk2 rhs_pk2
  3  from
  4  (select * from t1 where pk1=1) lhs
  5  full outer join
  6  (select * from t2 where pk1 in (2)) rhs on (lhs.pk2=rhs.pk2)
  7  )
  8* where lhs_pk2 is null or rhs_pk2 is null
SQL> /

   LHS_PK1    LHS_PK2    RHS_PK1    RHS_PK2
---------- ---------- ---------- ----------
         1          1
                               2          4

SQL> c/(2)/(3)
  6* (select * from t2 where pk1 in (3)) rhs on (lhs.pk2=rhs.pk2)
SQL> /

   LHS_PK1    LHS_PK2    RHS_PK1    RHS_PK2
---------- ---------- ---------- ----------
         1          1
                               3          4

SQL> c/(3)/(4)
  6* (select * from t2 where pk1 in (4)) rhs on (lhs.pk2=rhs.pk2)
SQL> /

   LHS_PK1    LHS_PK2    RHS_PK1    RHS_PK2
---------- ---------- ---------- ----------
         1          1
         1          2
                               4          4
                               4          5

Those are the 8 (2+2+4) rows I want in 3 separate queries i.e. when I iterate over t2.pk1

But when I combine them in one query like

SQL> l
  1  select * from  (
  2  select lhs.pk1 lhs_pk1,lhs.pk2 lhs_pk2,rhs.pk1 rhs_pk1,rhs.pk2 rhs_pk2
  3  from
  4  (select * from t1 where pk1=1) lhs
  5  full outer join
  6  (select * from t2 where pk1 in (2,3,4)) rhs on (lhs.pk2=rhs.pk2)
  7  )
  8* where lhs_pk2 is null or rhs_pk2 is null
SQL> /

   LHS_PK1    LHS_PK2    RHS_PK1    RHS_PK2
---------- ---------- ---------- ----------
         1          1
                               2          4
                               3          4
                               4          4
                               4          5

5 rows selected.

I get only 5 rows, where are the missing 3 rows?

Thanks 

Tom,
 How is it possible to achieve the equivalent of an outer join to 2 different tables?

Consider the scenario I have 

create table cm (
icn varchar2(10),
r_id varchar2(5),
firstdos date) -- the first date of service on the claim;

insert into cm
values('1098060011', '10010', to_date('07/10/2006', 'mm/dd/yyyy'));
insert into cm
values('1098060012', '10010', to_date('07/10/2006', 'mm/dd/yyyy'));
insert into cm
values('1098060013', '10020', to_date('07/10/2006', 'mm/dd/yyyy'));

create table cd(
icn varchar2(10),
dos date, - dates of service for detail
line varchar2(2));

insert into cd
values('1098060011', to_date('07/01/2006', 'mm/dd/yyyy'), '01');

insert into cd
values('1098060011', to_date('07/02/2006', 'mm/dd/yyyy'), '02');

insert into cd
values('1098060012', to_date('07/10/2006', 'mm/dd/yyyy'), '01');

insert into cd
values('1098060012', to_date('07/15/2006', 'mm/dd/yyyy'), '02');

insert into cd
values('1098060013', to_date('07/10/2006', 'mm/dd/yyyy'), '01');

insert into cd
values('1098060013', to_date('07/02/2006', 'mm/dd/yyyy'), '02');

create table rm(
r_id varchar2(5),
medstat varchar2(2),
beg_date date,
end_date date)

insert into rm
values('10010', 'SE', to_date('06/01/2006', 'mm/dd/yyyy'), to_date('07/01/2006', 'mm/dd/yyyy'));

insert into rm
values('10010', 'SC', to_date('07/02/2006', 'mm/dd/yyyy'), to_date('07/10/2006', 'mm/dd/yyyy'));

insert into rm
values('10020', 'SE', to_date('07/12/2006', 'mm/dd/yyyy'), to_date('07/20/2006', 'mm/dd/yyyy'));


CM and CD are linked by ICN. They have a one to many relationship
CM and RM are linked by r_id. Again a one to many relationship.

What I'm trying here is to retrieve ALL the records from CM and CD and those corresponding records from RM that where the 'DOS' from CD falls between beg_date and end_date of RM. 

I tried this and I dont get the required answer for the obvious reasons
SQL> select cm.icn, cm.r_id, cd.line, cm.firstdos, cd.dos, rm.medstat, rm.beg_date, rm.end_date
  2  from cm, cd, rm
  3  where cm.icn = cd.icn
  4  and cm.r_id = rm.r_id(+)
  5  and cd.dos between nvl(beg_date, to_date('01/01/1900', 'mm/dd/yyyy')) and nvl(end_date, to_date('01/01/1900', 'mm/dd/yyyy'))
  6  /

ICN        R_ID  LI FIRSTDOS  DOS       ME BEG_DATE  END_DATE
---------- ----- -- --------- --------- -- --------- ---------
1098060011 10010 01 01-JUL-06 01-JUL-06 SE 01-JUN-06 01-JUL-06
1098060011 10010 02 01-JUL-06 02-JUL-06 SC 02-JUL-06 10-JUL-06
1098060012 10010 01 10-JUL-06 10-JUL-06 SC 02-JUL-06 10-JUL-06
1098060013 10020 02 10-JUL-06 12-JUL-06 SE 12-JUL-06 20-JUL-06

I came up with the answer, but am not sure if its the correct answer or the 'right' approach and was wondering if you have a different approach.
SQL>  select cm.icn, cm.r_id, cd.line, cm.firstdos, cd.dos, rm.medstat, rm.beg_date, rm.end_date
  2   from cm, cd, rm
  3   where cm.icn = cd.icn
  4   and cm.r_id = rm.r_id(+)
  5   and cd.dos between nvl(beg_date, to_date('01/01/1900', 'mm/dd/yyyy')) and nvl(end_date, to_date('01/01/2099', 'mm/dd/yyyy'))
  6    union all
  7   select cm.icn, cm.r_id, cd.line, cm.firstdos, cd.dos, null medstat, null beg_date, null end_date
  8   from cm, cd
  9   where cm.icn = cd.icn
 10   and not exists (select 'x' from rm
 11                    where rm.r_id = cm.r_id
 12        and cd.dos between nvl(beg_date, to_date('01/01/1900', 'mm/dd/yyyy')) and nvl(end_date, to_date('01/01/2099', 'mm/dd/yyyy')))
 13  order by icn, line;

ICN        R_ID  LI FIRSTDOS  DOS       ME BEG_DATE  END_DATE
---------- ----- -- --------- --------- -- --------- ---------
1098060011 10010 01 01-JUL-06 01-JUL-06 SE 01-JUN-06 01-JUL-06
1098060011 10010 02 01-JUL-06 02-JUL-06 SC 02-JUL-06 10-JUL-06
1098060012 10010 01 10-JUL-06 10-JUL-06 SC 02-JUL-06 10-JUL-06
1098060012 10010 02 10-JUL-06 15-JUL-06
1098060013 10020 01 10-JUL-06 10-JUL-06
1098060013 10020 02 10-JUL-06 12-JUL-06 SE 12-JUL-06 20-JUL-06

6 rows selected.

Any comments on a different approach?

Thanks a lot,




 

I'd like to get all the departments and its employees info, including departments that don't have any employees. However, I'd like to filter out employess that don't have a manager.  How do I do that?

SQL> SELECT d.dname, e.ename, e.mgr
  2  FROM   scott.dept d, scott.emp 
  3  WHERE  d.deptno = e.deptno (+)
  4  /

DNAME          ENAME             MGR
-------------- ---------- ----------
ACCOUNTING     CLARK            7839
ACCOUNTING     KING                  --> want to eliminate this
ACCOUNTING     MILLER           7782
RESEARCH       JONES            7839
RESEARCH       FORD             7566
RESEARCH       ADAMS            7788
RESEARCH       SMITH            7902
RESEARCH       SCOTT            7566
SALES          WARD             7698
SALES          TURNER           7698
SALES          ALLEN            7698
SALES          JAMES            7698
SALES          BLAKE            7839
SALES          MARTIN           7698
OPERATIONS

15 rows selected.
 

CREATE TABLE ROSTERS
(
  DEPOT                 VARCHAR2(15 BYTE)       NOT NULL,
  RESOURCE_TYPE         VARCHAR2(15 BYTE)       NOT NULL,
  ROSTER_TYPE           VARCHAR2(15 BYTE)       NOT NULL,
  ROSTER_ID             VARCHAR2(8 BYTE)        NOT NULL,
  WEEK_ENDING           DATE,
  CYCLE                 VARCHAR2(15 BYTE),
  CYCLE_SEQ             NUMBER
);

ALTER TABLE ROSTERS ADD (
  CONSTRAINT ROSTERS_PK
 PRIMARY KEY
 (DEPOT, RESOURCE_TYPE, ROSTER_ID, ROSTER_TYPE));



CREATE TABLE ROSTER_SLOTS
(
  SLOT_SYSID        NUMBER(12)                  NOT NULL,
  ROSTER_ID         VARCHAR2(8 BYTE)            NOT NULL,
  DEPOT             VARCHAR2(15 BYTE)           NOT NULL,
  ROSTER_TYPE       VARCHAR2(15 BYTE)           NOT NULL,
  RESOURCE_TYPE     VARCHAR2(15 BYTE)           NOT NULL,
  SLOT_NO           NUMBER(3)                   NOT NULL,
  RESOURCE_ID       NUMBER(5),
  SEQUENCE          NUMBER(2)                   NOT NULL,
  SUN_WK_ID         VARCHAR2(15 BYTE),
  SUN_WK_TYPE       VARCHAR2(1 BYTE),
  SUN_TIME_ON       VARCHAR2(5 BYTE),
  SUN_TIME_OFF      VARCHAR2(5 BYTE),
  MON_WK_ID         VARCHAR2(15 BYTE),
  MON_WK_TYPE       VARCHAR2(1 BYTE),
  MON_TIME_ON       VARCHAR2(5 BYTE),
  MON_TIME_OFF      VARCHAR2(5 BYTE),
  TUE_WK_ID         VARCHAR2(15 BYTE),
  TUE_WK_TYPE       VARCHAR2(1 BYTE),
  TUE_TIME_ON       VARCHAR2(5 BYTE),
  TUE_TIME_OFF      VARCHAR2(5 BYTE)
);

CREATE UNIQUE INDEX ROSTER_SLOTS_PK ON ROSTER_SLOTS
(SLOT_SYSID);

ALTER TABLE ROSTER_SLOTS ADD (
  CONSTRAINT RSLOTS_ROSTER_FK 
 FOREIGN KEY (DEPOT, RESOURCE_TYPE, ROSTER_ID, ROSTER_TYPE) 
 REFERENCES ROSTERS (DEPOT,RESOURCE_TYPE,ROSTER_ID,ROSTER_TYPE));

select *
from rosters
;

--  You will find cycle 'CYCLEAB' and 'CYCLEAC' have two weeks (by cycle_seq) and CYCLE27 and CYCLEAA only have one week

select r.cycle, r.cycle_seq, s.roster_id, count(*)
from rosters r, roster_slots s
where r.roster_id = s.roster_id 
group by r.cycle, r.cycle_seq, s.roster_id
order by cycle, cycle_seq
;

CYCLE CYCLE_SEQ ROSTER_ID COUNT(*)
CYCLE27 1       PIC4/5Z    12
CYCLEAA 1       PIC4/5AA    10
CYCLEAB 1       PIC4/5AB    66
CYCLEAB 2       PIC4/5AC    69
CYCLEAC 1       PIC4/5AD    62
CYCLEAC 2       PIC4/5AE    62

-- Also the number of records in ROSTER_SLOTS might not be the same for week 1 and week 2 within the cycle (ie. CYCLEAB)

--  I need to create a view that return all records on both table. 
select  r1.cycle wk1_cycle, r1.cycle_seq wk1_cycle_seq, r1.roster_id wk1_roster_id 
       , w1.slot_sysid wk1_slot_sysid, w1.slot_no wk1_slot_no, w1.sequence wk1_sequence
       , w1.resource_id wk1_resource_id
       , r2.cycle wk2_cycle, r2.cycle_seq wk2_cycle_seq, r2.roster_id wk2_roster_id
       , w2.slot_sysid wk2_slot_sysid, w2.slot_no wk2_slot_no, w2.sequence wk2_sequence
       , w2.resource_id wk2_resource_id 
from rosters r1 
  join roster_slots w1 on ( 
      r1.depot = w1.depot and 
      r1.roster_type = w1.roster_type and 
      r1.resource_type = w1.resource_type and 
      r1.roster_id = w1.roster_id
  --and   w1.slot_no = 1
  ) 
  left outer join rosters r2 on ( 
      r1.depot = r2.depot and  
      r1.roster_type = r2.roster_type and  
      r1.resource_type = r2.resource_type and  
      r1.cycle = r2.cycle and
      r2.cycle_seq = 2   -- Week 2 of the Cycle 
      ) 
  left outer join roster_slots w2 on ( 
      r2.depot = w2.depot and 
      r2.roster_type = w2.roster_type and 
      r2.resource_type = w2.resource_type and 
      r2.roster_id = w2.roster_id
  --and w2.slot_no = 1
    ) 
where r1.cycle_seq = 1    -- Week 1 of the cycle 
order by r1.roster_id, w1.slot_no, w1.sequence
;

-- The question is, how do I join W1 to W2 on these column below, bare in mind there might not be W2 that matches to W1 on those
--  join criterias. 

and w1.depot = w2.depot 
and w1.roster_type = w2.roster_type 
and w1.resource_type = w2.resource_type 
and w1.slot_no = w2.slot_no 
and w1.resource_id = w2.resource_id 
and w1.sequence = w2.sequence 

Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48327, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 1, 1, 10104, 'RDO', '2526', '01:30', '09:30', '3528', '06:30', '14:30');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (49655, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 1, 1, 19691, 'RDO', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID)
 Values
   (50982, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 1, 1, 19985, 'RDO', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (52338, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 1, 1, 97130, 'RDO', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (53659, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 1, 1, 97130, 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (54967, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 1, 1, 97130, 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48328, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 2, 1, 19809, 'Y2SU', '09:00', '18:00', '2881', '06:00', '15:00', 'L1TU', '08:55', '17:55');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (49656, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 2, 1, 10104, '1238', '05:00', '13:00', '2824', '10:15', '18:15', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (50983, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 2, 1, 19691, 'NO TASK', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (52339, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 2, 1, 19985, 'LEARN', '04:10', '12:10', 'LEARN', '04:50', '12:50', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (53660, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 2, 1, 19985, '1235', '14:00', '22:00', '2235', '14:00', '22:00', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (54968, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 2, 1, 19985, 'NO TASK', 'C2MO', '14:00', '23:00', '3534', '15:45', '23:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48329, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 3, 1, 18129, '1526', '06:45', '14:45', 'W2MO', '16:45', '00:45', 'C2TU', '14:00', '23:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (49657, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 3, 1, 19809, 'W2SU', '16:45', '00:45', 'W2MO', '16:45', '00:45', 'C2TU', '14:00', '23:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (50984, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 3, 1, 10104, '1235', '14:00', '22:00', 'P2MO', '13:30', '21:30', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (52340, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 3, 1, 19691, 'NO TASK', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (53661, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 3, 1, 19691, 'NO TASK', 'ANNUAL', '3540', '14:00', '22:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (54969, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 3, 1, 19691, 'RDO', '3528', '06:30', '14:30');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48330, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 4, 1, 96081, 'C3SU', '22:00', '07:00', 'L2MO', '23:40', '08:40', '3826', '22:45', '06:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (49658, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 4, 1, 18129, 'C3SU', '22:00', '07:00', 'A2MO', '21:00', '06:00', '3826', '22:45', '06:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (50985, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 4, 1, 19809, 'NO TASK', 'C2MO', '14:00', '23:00', '3534', '15:45', '23:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (52341, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 4, 1, 10104, 'NO TASK', 'C1MO', '06:00', '15:00', '3534', '15:45', '23:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (53662, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 4, 1, 10104, 'RDO', '3ST9', '06:30', '14:30');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (54970, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 4, 1, 10104, 'Y1MO', '01:00', '10:00', 'W1TU', '04:45', '14:15');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48331, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 5, 1, 14650, '1824', '09:15', '17:15', 'M1TU', '04:40', '12:40');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (49659, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 5, 1, 96081, 'RDO', 'SHED', '04:40', '12:40');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (50986, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 5, 1, 18129, 'RDO', '3528', '06:30', '14:30');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (52342, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 5, 1, 19809, 'RDO', '3528', '06:30', '14:30');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (53663, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 5, 1, 19809, 'REQUEST', 'Y1MO', '01:00', '10:00', '3LT9', '04:00', '12:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (54971, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 5, 1, 19809, 'Y2MO', '09:00', '18:00', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48332, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 6, 1, 10087, '1526', '06:45', '14:45', 'WIMO', '04:45', '12:45', 'LEARN', '04:50', '12:50');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (49660, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 6, 1, 14650, 'NO TASK', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (50987, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 6, 1, 96081, 'PISU', '04:10', '12:10', 'Y1MO', '01:00', '10:00', 'Y1TU', '01:00', '10:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (52343, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 6, 1, 18129, 'PISU', '04:10', '12:10', 'Y1MO', '01:00', '10:00', 'Y1TU', '01:00', '10:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (53664, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AD', 6, 1, 18129, 'L1SU', '09:10', '18:10', 'Y2MO', '09:00', '18:00', 'RDO');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (54972, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AE', 6, 1, 18129, 'W2SU', '20:45', '05:45', 'C3MO', '22:00', '07:00', 'W2TU', '20:45', '05:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (48333, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5Z', 7, 1, 19731, 'C2SU', '14:00', '23:00', 'S2MO', '15:40', '00:40', '3235', '14:00', '22:00');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, SUN_TIME_ON, SUN_TIME_OFF, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID, TUE_TIME_ON, TUE_TIME_OFF)
 Values
   (49661, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AA', 7, 1, 10087, 'C2SU', '14:00', '23:00', 'S2MO', '15:40', '00:40', 'W2TU', '16:45', '00:45');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, TUE_WK_ID)
 Values
   (50988, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AB', 7, 1, 14650, 'NO TASK', 'ANNUAL', 'ANNUAL');
Insert into ROSTER_SLOTS
   (SLOT_SYSID, DEPOT, RESOURCE_TYPE, ROSTER_TYPE, ROSTER_ID, SLOT_NO, SEQUENCE, RESOURCE_ID, SUN_WK_ID, MON_WK_ID, MON_TIME_ON, MON_TIME_OFF, TUE_WK_ID)
 Values
   (52344, 'PICTON', 'LOCO L4/5', 'OPERATIONAL', 'PIC4/5AC', 7, 1, 96081, 'REQUEST', 'C2MO', '14:00', '23:00', 'RDO');

create or replace view roster_vw as
with all_slots as (
   select distinct r.depot, r.roster_type, r.resource_type, r.cycle, s.slot_no, s.sequence
   from   rosters r join roster_slots s on ( r.depot = s.depot and
                                             r.resource_type = s.resource_type and
                                             r.roster_id = s.roster_id and
                                             r.roster_type = s.roster_type                                              
                                             )
   )
select col1,......
from  all_slots left outer join (
         select col1,.....               
         from  rosters r1  left outer join roster_slots s1 on ( r1.depot = s1.depot and
                                                                r1.resource_type = s1.resource_type and
                                                                r1.roster_type = s1.roster_type and
                                                                r1.roster_id = s1.roster_id 
                                                              )
         where r1.cycle_seq = 1  -- Week 1
         -- << I need the filters to apply here >>
       ) w1  on ( all_slots.depot = w1.depot and
              all_slots.roster_type = w1.roster_type and
          all_slots.resource_type = w1.resource_type and
          all_slots.cycle = w1.cycle and
              all_slots.slot_no = w1.slot_no and
              all_slots.sequence = w1.sequence )
       left outer join (
         select  col1,...
         from  rosters r1  left outer join roster_slots s1 on ( r1.depot = s1.depot and
                            r1.resource_type = s1.resource_type and
                                                                r1.roster_type = s1.roster_type and
                                                                r1.roster_id = s1.roster_id
                                                              )
         where  r1.cycle_seq = 2   -- Week 2
         -- << and here... >>
       ) w2  on ( all_slots.depot = w2.depot and
              all_slots.roster_type = w2.roster_type and
          all_slots.resource_type = w2.resource_type and
          all_slots.cycle = w2.cycle and
              all_slots.slot_no = w2.slot_no and
              all_slots.sequence = w2.sequence );  

select *
from roster_vw
where depot = 'DEPOT'
and   roster_type  = 'ROSTER'
and   resource_type = 'RESOURCE'
and   cycle = 'CYCLE';

The explain plan as follow

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [ALL_ROWS] Cost = 1696
  VIEW  CRS_CYCLE_ROSTERS_VW
    HASH JOIN OUTER
      HASH JOIN OUTER
        VIEW
          HASH UNIQUE
            NESTED LOOPS
              INDEX RANGE SCAN RS_PK [ANALYZED]
              TABLE ACCESS BY INDEX ROWID ROSTERS [ANALYZED]
                INDEX UNIQUE SCAN ROS_PK [ANALYZED]
        VIEW
          NESTED LOOPS OUTER
            TABLE ACCESS FULL ROSTERS [ANALYZED]
            TABLE ACCESS BY INDEX ROWID ROSTER_SLOTS [ANALYZED]
              INDEX RANGE SCAN RS_PK [ANALYZED]
      VIEW
        NESTED LOOPS OUTER
          TABLE ACCESS FULL ROSTERS [ANALYZED]
          TABLE ACCESS BY INDEX ROWID ROSTER_SLOTS [ANALYZED]
            INDEX RANGE SCAN RS_PK [ANALYZED]

I have a complex query, It like this.
mh > select RegionGrid.regionid,
  2                 max(nvl(LSGrid.distributionitemvalue,1)) maxvalue
  3             from
  4            (select DR.regionid, DI.distributionitemseq
  5               from distributions D
  6              inner join distributionregions DR
  7                 on (D.distributionid = DR.distributionid)
  8              inner join distributionitems DI
  9                 on (D.distributionid = DI.distributionid)
 10              where DI.distributionitemexpdt is null
 11                and D.DISTRIBUTIONEXPDT is null
 12                and DR.EXPIRYDT is null
 13                and D.distributiontypeid = 54
 14                and DI.distributionitemvalue = 1) RegionGrid
 15           left outer join
 16           (select DI.distributionitemseq,
 17                   DI.distributionitemvalue
 18              from distributionitems DI
 19             inner join distributions D
 20                on (D.DISTRIBUTIONID = DI.DISTRIBUTIONID)
 21             where DI.distributionitemexpdt is null
 22               and D.DISTRIBUTIONEXPDT is null
 23               and DI.distributionid=8455) LSGrid
 24          on (RegionGrid.distributionitemseq = LSGrid.distributionitemseq)
 25       group by regionid;

  REGIONID   MAXVALUE
---------- ----------
         0          0
       105          0
       106          0
       107          0
       108          0
       109          0
       110          0
       111          0
       112          0
       113          0

10 rows selected.

If I use left outer join, it changes the values.
mh > select R.regionid, nvl(regionmax.maxvalue,0)
  2         from regions R
  3         left outer join
  4          (select RegionGrid.regionid,
  5                 max(nvl(LSGrid.distributionitemvalue,1)) maxvalue
  6             from
  7            (select DR.regionid, DI.distributionitemseq
  8               from distributions D
  9              inner join distributionregions DR
 10                 on (D.distributionid = DR.distributionid)
 11              inner join distributionitems DI
 12                 on (D.distributionid = DI.distributionid)
 13              where DI.distributionitemexpdt is null
 14                and D.DISTRIBUTIONEXPDT is null
 15                and DR.EXPIRYDT is null
 16                and D.distributiontypeid = 54
 17                and DI.distributionitemvalue = 1) RegionGrid
 18           left outer join
 19           (select DI.distributionitemseq,
 20                   DI.distributionitemvalue
 21              from distributionitems DI
 22             inner join distributions D
 23                on (D.DISTRIBUTIONID = DI.DISTRIBUTIONID)
 24             where DI.distributionitemexpdt is null
 25               and D.DISTRIBUTIONEXPDT is null
 26               and DI.distributionid=8455) LSGrid
 27          on (RegionGrid.distributionitemseq = LSGrid.distributionitemseq)
 28       group by regionid) regionmax
 29       on (R.regionid = regionmax.regionid)
 30       order by regionid;

  REGIONID NVL(REGIONMAX.MAXVALUE,0)
---------- -------------------------
         0                         1
       105                         1
       106                         1
       107                         1
       108                         1
       109                         1
       110                         1
       111                         1
       112                         1
       113                         1

10 rows selected.

But If I use the inner join, it doesn't change value.
mh > select R.regionid, nvl(regionmax.maxvalue,0)
  2         from regions R
  3         Inner join
  4          (select RegionGrid.regionid,
  5                 max(nvl(LSGrid.distributionitemvalue,1)) maxvalue
  6             from
  7            (select DR.regionid, DI.distributionitemseq
  8               from distributions D
  9              inner join distributionregions DR
 10                 on (D.distributionid = DR.distributionid)
 11              inner join distributionitems DI
 12                 on (D.distributionid = DI.distributionid)
 13              where DI.distributionitemexpdt is null
 14                and D.DISTRIBUTIONEXPDT is null
 15                and DR.EXPIRYDT is null
 16                and D.distributiontypeid = 54
 17                and DI.distributionitemvalue = 1) RegionGrid
 18           left outer join
 19           (select DI.distributionitemseq,
 20                   DI.distributionitemvalue
 21              from distributionitems DI
 22             inner join distributions D
 23                on (D.DISTRIBUTIONID = DI.DISTRIBUTIONID)
 24             where DI.distributionitemexpdt is null
 25               and D.DISTRIBUTIONEXPDT is null
 26               and DI.distributionid=8455) LSGrid
 27          on (RegionGrid.distributionitemseq = LSGrid.distributionitemseq)
 28       group by regionid) regionmax
 29       on (R.regionid = regionmax.regionid)
 30       order by regionid;

  REGIONID NVL(REGIONMAX.MAXVALUE,0)
---------- -------------------------
         0                         0
       105                         0
       106                         0
       107                         0
       108                         0
       109                         0
       110                         0
       111                         0
       112                         0
       113                         0

10 rows selected.

Any one know why? Maybe it's an Oracle bug?
As my understanding, the inner join and outer join only affect the rows for output not for values. 

However, if I replace the second part on this left join query, it appears correct.
mh > select R.regionid, regionmax.maxvalue
  2         from regions R
  3         left outer join
  4          (select regionid, 0 as maxvalue from regions) regionmax
  5       on (R.regionid = regionmax.regionid)
  6       order by regionid
  7  ;

  REGIONID   MAXVALUE
---------- ----------
         0          0
       105          0
       106          0
       107          0
       108          0
       109          0
       110          0
       111          0
       112          0
       113          0

10 rows selected.

Hi Tom,
I feel, you are God to Oracle learners. At my desk I have put your photo. Thanks for great forum.

I have one question, please help me.

SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 SAL                                                NUMBER



SQL> desc b;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(10)
 AGE                                                NUMBER
 COMM                                               NUMBER



SQL> select * from a;

NAME              AGE        SAL
---------- ---------- ----------
s                  10       1000
r                  20      10000



SQL> select * from b;

no rows selected


I wanted to get output like below.

a.name    a.age    b.name    b.age
s         10       null      null    

I wrote something like below 

SQL> select * from a,b where a.name(+)='s' and a.age(+)=10 and b.name(+)=a.name
and b.age(+)=a.age;

NAME              AGE        SAL NAME     AGE       COMM
---------- ---------- ---------- ---------- ---------- ----------
s                  10       1000



One more thing, I use the above select query inside a procedure using ref cursor. These 's' and 10 are passed as input parameters to the procedures.

insert into b values('abcd',22,2000);


SQL> select * from b;

NAME              AGE       COMM
---------- ---------- ----------
abcd               22       2000

Now I will pass the values as 'abcd' and 22 to the select query. But this time it is not showing any result. 


SQL> select * from a,b where a.name(+)='abcd' and a.age(+)=22 and b.name(+)=a.na
me and b.age(+)=a.age;

no rows selected

The output should be as,

NAME              AGE        SAL NAME    AGE       COMM
---------- ---------- ---------- ---------- ---------- ----------
                                 abcd     22       2000


Please suggest me a solution.

Regards.

This is with respect to the very first posting in the thread. You have indicated that 


from t1, t2
 where t1.x = t2.x(+)
   and t2.any_other_column <some condition>


is a performance drain. I fully agree with you. 
I am facing the same issue, where in the developers have written a similar kind of  "where" clause which does not need a (+) 

The argument is I need to prove ,by  removing the (+) must improve upon the response time COMPARITIVELY. 

I get response times very identical i.e whether I use (+) or NOT  I get the same timings on the query

Please help Attached is the autotrace ( it is 10g) 

SQL>  select *
  2   from flight_seg currentfli0_ left outer join cust_jrny_version custjrnyve1_
  3   on currentfli0_.CUST_JRNY_ID=custjrnyve1_.CUST_JRNY_ID
  4   and currentfli0_.ENTRY_VERSION_NUMBER=custjrnyve1_.VERSION_NUMBER  left outer join cust_jrny customerjo2_
  5   on custjrnyve1_.CUST_JRNY_ID=customerjo2_.CUST_JRNY_ID left outer join subscriber subscriber3_
  6   on customerjo2_.SUBSCRIBER_ID=subscriber3_.SUBSCRIBER_ID
  7   where currentfli0_.CUST_JRNY_ID=13 and currentfli0_.HISTORY_VERSION_NUMBER=0;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3981926879

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |    18E|    15E|    18E  (0)|999:59:59 |       |       |
|   1 |  NESTED LOOPS OUTER                   |                          |    18E|    15E|    18E  (0)|999:59:59 |       |       |
|   2 |   NESTED LOOPS OUTER                  |                          |    18E|    15E|    18E  (0)|999:59:59 |       |       |
|   3 |    NESTED LOOPS OUTER                 |                          |    18E|    15E|    59P  (1)|999:59:59 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| FLIGHT_SEG               |    59P|  4364P|     3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                 | FS_CUST_JRNY_VERSION_FK2 |    59P|       |     1   (0)| 00:00:01 |       |       |
|   6 |     TABLE ACCESS BY GLOBAL INDEX ROWID| CUST_JRNY_VERSION        |   493G|    29T|     1   (0)| 00:00:01 | ROWID | ROWID |
|*  7 |      INDEX UNIQUE SCAN                | CUST_JRNY_VERSION_PK     |     1 |       |     0   (0)| 00:00:01 |       |       |
|   8 |    TABLE ACCESS BY GLOBAL INDEX ROWID | CUST_JRNY                |   825G|    36T|     1   (0)| 00:00:01 | ROWID | ROWID |
|*  9 |     INDEX UNIQUE SCAN                 | CUST_JRNY_PK             |     1 |       |     0   (0)| 00:00:01 |       |       |
|  10 |   TABLE ACCESS BY INDEX ROWID         | SUBSCRIBER               | 33333 |   846K|     1   (0)| 00:00:01 |       |       |
|* 11 |    INDEX UNIQUE SCAN                  | SUBSCRIBER_PK            |     1 |       |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("CURRENTFLI0_"."CUST_JRNY_ID"=13 AND "CURRENTFLI0_"."HISTORY_VERSION_NUMBER"=0)
   7 - access("CUSTJRNYVE1_"."CUST_JRNY_ID"(+)=13 AND "CURRENTFLI0_"."ENTRY_VERSION_NUMBER"="CUSTJRNYVE1_"."VERSION_NUMBER
              "(+))
   9 - access("CUSTJRNYVE1_"."CUST_JRNY_ID"="CUSTOMERJO2_"."CUST_JRNY_ID"(+))
  11 - access("CUSTOMERJO2_"."SUBSCRIBER_ID"="SUBSCRIBER3_"."SUBSCRIBER_ID"(+))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       4646  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL>
SQL>
SQL> select *
  2   from flight_seg currentfli0_ inner join cust_jrny_version custjrnyve1_
  3   on currentfli0_.CUST_JRNY_ID=custjrnyve1_.CUST_JRNY_ID
  4   and currentfli0_.ENTRY_VERSION_NUMBER=custjrnyve1_.VERSION_NUMBER  inner join cust_jrny customerjo2_
  5   on custjrnyve1_.CUST_JRNY_ID=customerjo2_.CUST_JRNY_ID inner join subscriber subscriber3_
  6   on customerjo2_.SUBSCRIBER_ID=subscriber3_.SUBSCRIBER_ID
  7   where currentfli0_.CUST_JRNY_ID=13 and currentfli0_.HISTORY_VERSION_NUMBER=0;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2918300563

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |    18E|    15E|    59P  (1)|999:59:59 |       |       |
|   1 |  NESTED LOOPS                         |                          |    18E|    15E|    59P  (1)|999:59:59 |       |       |
|   2 |   NESTED LOOPS                        |                          |    59P|  8309P|     6   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                       |                          |     1 |    75 |     3   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| CUST_JRNY                |     1 |    49 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX UNIQUE SCAN                | CUST_JRNY_PK             |     1 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     TABLE ACCESS BY INDEX ROWID       | SUBSCRIBER               |   200K|  5078K|     1   (0)| 00:00:01 |       |       |
|*  7 |      INDEX UNIQUE SCAN                | SUBSCRIBER_PK            |     1 |       |     0   (0)| 00:00:01 |       |       |
|   8 |    TABLE ACCESS BY GLOBAL INDEX ROWID | FLIGHT_SEG               |    59P|  4364P|     3   (0)| 00:00:01 | ROWID | ROWID |
|*  9 |     INDEX RANGE SCAN                  | FS_CUST_JRNY_VERSION_FK2 |    59P|       |     1   (0)| 00:00:01 |       |       |
|  10 |   TABLE ACCESS BY GLOBAL INDEX ROWID  | CUST_JRNY_VERSION        |   493G|    29T|     1   (0)| 00:00:01 | ROWID | ROWID |
|* 11 |    INDEX UNIQUE SCAN                  | CUST_JRNY_VERSION_PK     |     1 |       |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("CUSTOMERJO2_"."CUST_JRNY_ID"=13)
   7 - access("CUSTOMERJO2_"."SUBSCRIBER_ID"="SUBSCRIBER3_"."SUBSCRIBER_ID")
   9 - access("CURRENTFLI0_"."CUST_JRNY_ID"=13 AND "CURRENTFLI0_"."HISTORY_VERSION_NUMBER"=0)
  11 - access("CUSTJRNYVE1_"."CUST_JRNY_ID"=13 AND "CURRENTFLI0_"."ENTRY_VERSION_NUMBER"="CUSTJRNYVE1_"."VERSION_NUMBER")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       4646  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed