正确:
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) as SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a
left join ty_bz b on a.billid = b.pk_tybz
left join ty_bzjy_bg_h ss on a.billid = ss.pk_tyjy_bg
left join sm_user c on (b.voperatorid = c.cuserid or
ss.voperatorid = c.cuserid)
left join bd_billtype d on (b.pk_billtype = d.pk_billtypecode or
ss.pk_billtype = d.pk_billtypecode)
where (a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL')
and a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ')
and a.checkman = '0001V31000000003O5G9'
order by a.senddate desc错误: select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and (b.voperatorid = c.cuserid(+) or ss.voperatorid = c.cuserid(+))//ORA-01719
and (b.pk_billtype = d.pk_billtypecode(+) or
ss.pk_billtype = d.pk_billtypecode(+))//ORA-01719
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
order by a.senddate desc请各位大虾请教,修改。谢谢
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) as SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a
left join ty_bz b on a.billid = b.pk_tybz
left join ty_bzjy_bg_h ss on a.billid = ss.pk_tyjy_bg
left join sm_user c on (b.voperatorid = c.cuserid or
ss.voperatorid = c.cuserid)
left join bd_billtype d on (b.pk_billtype = d.pk_billtypecode or
ss.pk_billtype = d.pk_billtypecode)
where (a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL')
and a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ')
and a.checkman = '0001V31000000003O5G9'
order by a.senddate desc错误: select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and (b.voperatorid = c.cuserid(+) or ss.voperatorid = c.cuserid(+))//ORA-01719
and (b.pk_billtype = d.pk_billtypecode(+) or
ss.pk_billtype = d.pk_billtypecode(+))//ORA-01719
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
order by a.senddate desc请各位大虾请教,修改。谢谢
Cause: An outer join appears in an or clause.
Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select where (B)).
select * from (
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and b.voperatorid = c.cuserid(+) and ss.voperatorid != c.cuserid(+)
and b.pk_billtype = d.pk_billtypecode(+) and ss.pk_billtype != d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and ss.voperatorid = c.cuserid(+)
and b.pk_billtype = d.pk_billtypecode(+) and ss.pk_billtype != d.pk_billtypecode(+))
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and b.voperatorid = c.cuserid(+) and ss.voperatorid != c.cuserid(+)
and ss.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and ss.voperatorid = c.cuserid(+)
and ss.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
) order by senddate desc
没有吧.
记得以前只支持(+)的形式,现在支持left join了
必须是a left join b on a.id=b.id or
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and b.voperatorid = c.cuserid(+)
and b.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and b.voperatorid = c.cuserid(+)
and ss.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and ss.voperatorid = c.cuserid(+)
and b.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and a.billid = ss.pk_tyjy_bg(+)
and ss.voperatorid = c.cuserid(+)
and ss.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1', 'BJ') and
a.checkman = '0001V31000000003O5G9')
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bz b,
sm_user c,
bd_billtype d
where a.billid = b.pk_tybz(+)
and b.voperatorid = c.cuserid(+)
and b.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('CL', 'R3', 'JK', 'FJY', 'PO', 'J1') and
a.checkman = '0001V31000000003O5G9')union all
select a.pk_billtype,
c.user_name,
substr(a.senddate, 1, 10) SENDDATE,
a.billno,
a.checkman,
d.billtypename billname
from pub_workflownote a,
ty_bzjy_bg_h ss,
sm_user c,
bd_billtype d
where a.billid = ss.pk_tyjy_bg(+)
and ss.voperatorid = c.cuserid(+)
and ss.pk_billtype = d.pk_billtypecode(+)
and ((a.ischeck = 'N' and
(a.approvestatus in (0, 2) or a.approvestatus is null) and
a.dr = 0 and a.actiontype <> 'BIZ' and a.actiontype <> 'MAKEBILL') and
a.pk_billtype in ('BJ') and
a.checkman = '0001V31000000003O5G9')
order by senddate desc