SQL & PL/SQL

Hi All,

I want to display the data even if there is no corresposding data in the fac_pos table.

when using outer joins getting error message.

Any work around for this ? Please suggest. :-)

SQL> SELECT case when flen.FPID is not null then
  2        'do the calculations here'
  3        else
  4        'no value in the FAC_POS table so do the ELSE PART'
  5       end CASE , 
  6       mtf.EXT_FID
  7          FROM 
  8       D_F_MAP  MTF,
  9             FAC   EFAC,
 10             TRADING       EST,
 11             FAC_POS         FLEN,
 12             USERS_MAP custmap
 13       WHERE mtf.SRC_FID  = efac.FID (+)
 14         AND mtf.SRC_DID  = efac.DID  (+)
 15         AND efac.TFID = est.TFID  
 16         AND mtf.EXT_FID (+) = flen.FID               
 17         AND mtf.EXT_DID (+)  = flen.DID  
 18      AND custmap.SRC_CUST_ID   =  est.SID  (+)      
 19         AND custmap.EXT_CUST_ID  =  flen.CUSTID (+) 
 20      and est.TFID =14;

no rows selected

SQL> SELECT case when flen.FPID is not null then
  2        'do the calculations here'
  3        else
  4          'no value in the FAC_POS table so do the ELSE PART'
  5       end CASE , 
  6       flen.CUSTID FROM TRADING EST, USERS_MAP,FAC_POS FLEN,FAC EFAC, D_F_MAP  MTF
  7  WHERE
  8   EST.SID = USERS_MAP.SRC_CUST_ID        (+) AND
  9   USERS_MAP.EXT_CUST_ID   =  flen.CUSTID (+) AND 
 10   MTF.SRC_DID (+) = EFAC.DID         AND
 11   MTF.SRC_FID (+) = EFAC.FID        AND
 12   efac.TFID = est.TFID         AND 
 13   mtf.EXT_FID (+) = flen.FID                 AND          
 14   mtf.EXT_DID (+)  = flen.DID     AND 
 15   est.TFID =14
 16  /
 MTF.SRC_FID (+) = EFAC.FID        AND
                 *
ERROR at line 11:
ORA-01417: a table may be outer joined to at most one other table


create table D_F_MAP
(
  SOURCE  VARCHAR2(10) not null,
  SRC_DID VARCHAR2(8) not null,
  SRC_FID VARCHAR2(10) not null,
  EXT_DID VARCHAR2(20),
  EXT_FID VARCHAR2(20)
)
;
 
 
create table FAC
(
  TFID  NUMBER,
  SRC   VARCHAR2(10),
  DID   NUMBER,
  FID   NUMBER,
  CSAMT NUMBER
)
;
 
create table FAC_POS
(
  FPID   NUMBER,
  CUSTID NUMBER,
  SRC    VARCHAR2(10),
  DID    NUMBER,
  FID    NUMBER,
  SPOS   NUMBER
)
;
 
 
create table PASS_OVER
(
  TFID VARCHAR2(20) not null,
  FLG  VARCHAR2(1)
)
;
 
 
create table TRADING
(
  TFID  NUMBER not null,
  SRC   VARCHAR2(10),
  TDATE DATE,
  BID   NUMBER,
  SID   NUMBER
)
;
 
 
create table USERS_MAP
(
  SRC_CUST_ID VARCHAR2(8) not null,
  EXT_CUST_ID VARCHAR2(20),
  SRC         VARCHAR2(10) not null
)
;
 
 
insert into D_F_MAP (SOURCE, SRC_DID, SRC_FID, EXT_DID, EXT_FID)
values ('KP', '854', '7754', '101', '1202');
insert into D_F_MAP (SOURCE, SRC_DID, SRC_FID, EXT_DID, EXT_FID)
values ('KP', '4545', '4444', '504', '1604');
insert into D_F_MAP (SOURCE, SRC_DID, SRC_FID, EXT_DID, EXT_FID)
values ('KP', '7858', '9646', '604', '1705');
insert into D_F_MAP (SOURCE, SRC_DID, SRC_FID, EXT_DID, EXT_FID)
values ('MS', '8799', '4544', '987', '1654');
 
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (10, 'KP', 854, 7754, 85000);
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (11, 'KP', 854, 7754, 44000);
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (12, 'KP', 4545, 4444, 47000);
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (13, 'KP', 7858, 9646, 80000);
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (14, 'MS', 8799, 4544, 60000);
insert into FAC (TFID, SRC, DID, FID, CSAMT)
values (15, 'KP', 854, 7754, 66000);
 
insert into FAC_POS (FPID, CUSTID, SRC, DID, FID, SPOS)
values (94, 5555, 'EXT', 504, 1604, 6000);
insert into FAC_POS (FPID, CUSTID, SRC, DID, FID, SPOS)
values (90, 1111, 'EXT', 101, 1202, 1000);
insert into FAC_POS (FPID, CUSTID, SRC, DID, FID, SPOS)
values (91, 2222, 'EXT', 302, 3652, 1000);
insert into FAC_POS (FPID, CUSTID, SRC, DID, FID, SPOS)
values (92, 3333, 'EXT', 987, 1654, 6000);
insert into FAC_POS (FPID, CUSTID, SRC, DID, FID, SPOS)
values (93, 4444, 'EXT', 604, 1705, 9000);
 
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (10, 'KP', to_date('10-02-2009', 'dd-mm-yyyy'), 1548, 96751);
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (11, 'KP', to_date('02-02-2009', 'dd-mm-yyyy'), 5468, 7895);
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (12, 'KP', to_date('20-02-2009', 'dd-mm-yyyy'), 1258, 6985);
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (13, 'KP', to_date('22-02-2009', 'dd-mm-yyyy'), 5468, 7865);
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (14, 'MS', to_date('18-02-2009', 'dd-mm-yyyy'), 4669, 6893);
insert into TRADING (TFID, SRC, TDATE, BID, SID)
values (15, 'KP', to_date('20-02-2009', 'dd-mm-yyyy'), 1548, 6975);
 
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('9675', '1111', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('5468', '2222', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('6893', '3333', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('5468', '4444', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('7865', '5555', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('6975', '6666', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('6975', '7777', 'kp');
insert into USERS_MAP (SRC_CUST_ID, EXT_CUST_ID, SRC)
values ('6985', '8888', 'kp');

Thanks.