oracle中

浪潮软件有个功能(pb开发的client端软件),点击时报错:
ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)

SELECT 'L',     KCXED1_LSBH, KCXED1_DJRQ, KCXED1_SJDH, KCXED1_BMBH, KCXED1_JHBH, KCXED1_CPBH, KCXED1_CPBH, LSWLZD_GGXH, KCXED1_CPSL, KCXED1_CPPC, KCXED1_LRXM, KCXED1_BZ,   KCXED1_KCXM, KCXED1_KCSH, KCXED1_KCRQ, KCXED1_KCJZ, KCXED1_WCBZ, KCXED1_CPRQ, KCXED1_C1,  KCXED1_C2,  KCXED1_C3,   KCXED1_C4,   KCXED1_C5,  KCXED1_U1,  KCXED1_U2,  KCXED1_U3,  KCXED1_SFQR, KCXED1_KCXGSJ, KCXED1_YWBS, KCXED1_DWGC,  KCXED1_DWGC,  LSWLZD_C1,  LSWLZD_C2,  LSWLZD_C3,  LSWLZD_C4,   LSWLZD_C5,     LSWLZD_U1,     LSWLZD_U2,     LSWLZD_U3  from KCXED1,LSWLZD  where KCXED1_CPBH = LSWLZD_WLBH(+) AND KCXED1_DJRQ>='20090201' AND KCXED1_DJRQ<='20090216'  AND NVL(KCXED1_SJDH,' ') = '09020023'  or  NVL(KCXED1_SJDH,' ') = '09020030'

此语句的where 条件中,是这么写的:
where KCXED1_CPBH = LSWLZD_WLBH(+) AND KCXED1_DJRQ>='20090201' AND KCXED1_DJRQ<='20090216'  AND NVL(KCXED1_SJDH,' ') = '09020023'  or  NVL(KCXED1_SJDH,' ') = '09020030'
也就是用了外连接(OUTER JOIN),只不过 KCXED1_CPBH = LSWLZD_WLBH(+)  是左外连接,这个有点搞笑,左外连接时,"+"放在=的右边.呵呵....不知道oracle为啥这么放...

实例:

CREATE TABLE departments

(

 depID  NUMBER(38,0),

 depName VARCHAR2(20),

 delFlag NUMBER(1,0)

);

 
 select * from departments

 select * from employees

CREATE TABLE employees

(

 empID  NUMBER(38,0),

 empName VARCHAR2(20),

 depID  NUMBER(38,0),

 delFlag NUMBER(1,0)

);

 

INSERT INTO departments VALUES(1,'Finacle',0);

INSERT INTO departments VALUES(2,'Marketing',0);

INSERT INTO departments VALUES(3,'HR',1);

INSERT INTO departments VALUES(4,'IT',0);

 

INSERT INTO employees VALUES(1,'wbq',1,0);

INSERT INTO employees VALUES(2,'czh',2,0);

INSERT INTO employees VALUES(3,'chh',1,0);

INSERT INTO employees VALUES(4,'wal',2,0);

INSERT INTO employees VALUES(5,'ddd',3,0);

 

COMMIT;

请执行
select * from employees  e , departments  d where e.depid = d.depid(+)

和

select * from employees  e , departments  d where e.depid (+) = d.depid

比较一下,这2个sql的异同

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/161195/viewspace-588623/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/161195/viewspace-588623/