原来在SYBASE下有这样的SQL。select
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substring(b.cd,1,2) =* a.b_cd
and b.ntype = 'D'
and b.up = '00'
and c.b_cd =* a.b_cd
and d.grp_cd + d.rate_cd =* c.grp_cd + c.rate_cd当转到ORACLE后,我对这条SQL进行了修改如下:select
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substr(b.cd(+),1,2) = a.b_cd
and b.ntype(+) = 'D'
and b.up(+) = '00'
and c.b_cd(+) = a.b_cd
and d.grp_cd(+) || d.rate_cd(+) = c.grp_cd(+) || c.rate_cd(+)执行报错。请问,该如何修改。
请大家帮帮忙,我的SQL水平不是太好。
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substring(b.cd,1,2) =* a.b_cd
and b.ntype = 'D'
and b.up = '00'
and c.b_cd =* a.b_cd
and d.grp_cd + d.rate_cd =* c.grp_cd + c.rate_cd当转到ORACLE后,我对这条SQL进行了修改如下:select
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substr(b.cd(+),1,2) = a.b_cd
and b.ntype(+) = 'D'
and b.up(+) = '00'
and c.b_cd(+) = a.b_cd
and d.grp_cd(+) || d.rate_cd(+) = c.grp_cd(+) || c.rate_cd(+)执行报错。请问,该如何修改。
请大家帮帮忙,我的SQL水平不是太好。
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a
left join
B b
on
a.b_cd = substr(b.cd,1,2) and b.ntype = 'D' and b.up = '00'
left join
C c
on
a.b_cd = c.b_cd
left join
D d
on
d.grp_cd||d.rate_cd=c.grp_cd||c.rate_cd
where
a.name = 'testName'
and
(a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
另外,如果不用XXX join,而用(+)能不能实现呢?
我这个问题很急的。最好能用(+)来实现,因为这样就不用修改from中的内容了,现在这段sql是嵌入在java程序中的,里面把from和where都单独封装了起来,如果from和where都修改,对我来说要增加很大的工作量和工作难度。如果不能用(+)来实现,也请大家说明原因。谢谢。
select
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substr(b.cd(+),1,2) = a.b_cd
and b.ntype(+) = 'D'
and b.up(+) = '00'
and c.b_cd(+) = a.b_cd
and d.grp_cd(+) || d.rate_cd(+) = c.grp_cd || c.rate_cd只是把c.grp_cd(+) || c.rate_cd(+)后面的(+)去掉不就可以了。
经我测试和你sybase下的程序结果是一样的。
我的修改脚本如下,请试试
select
a.id,
b.id, b.cd, b.up,
c.b_cd,
d.grp_cd, d.rate_cd, d.age
from
A a, B b, C c, D d
where
a.name = 'testName'
and (a.sale_fromdt <= '20070501' and a.sale_todt >= '20070501')
and substr(b.cd(+),1,2) =a.b_cd
and b.ntype(+) = 'D'
and b.up(+) = '00'
and c.b_cd(+) = a.b_cd
and d.grp_cd(+) || d.rate_cd(+) = c.grp_cd || c.rate_cd