select t.orderid,
decode(t.orderstates,'1000000001','失败交易','1000000002','失败交易','1000000003','失败交易','1000000004','可疑交易','1000000005','可疑交易','1000000006','可疑交易','1000000007','成功交易','1000000008',decode(d.chargflag,'0','审批通过,未充值','1','补充值完毕','3','补充值失败'),'1000000009','退款处理中','1000000010','退款失败','1000000011','退款成功','1000000012','退款失败','1000000013','退款失败','1000000014','退款失败') orderstates
from v_Tlbankorder t,
(select cityid,cityname from bicityinfotb) b,
(select yktid , fullname from biyktinfotb)c,
(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d where 1=1 and orderstates<>'1000000001' and userid=147
and t.cityid=b.cityid(+) and t.yktid=c.yktid(+)标红的就是出问题的地,我不知道(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d 的where t.orderid怎么与tlrepairchargetb 里的orderid关联,求高手指点
decode(t.orderstates,'1000000001','失败交易','1000000002','失败交易','1000000003','失败交易','1000000004','可疑交易','1000000005','可疑交易','1000000006','可疑交易','1000000007','成功交易','1000000008',decode(d.chargflag,'0','审批通过,未充值','1','补充值完毕','3','补充值失败'),'1000000009','退款处理中','1000000010','退款失败','1000000011','退款成功','1000000012','退款失败','1000000013','退款失败','1000000014','退款失败') orderstates
from v_Tlbankorder t,
(select cityid,cityname from bicityinfotb) b,
(select yktid , fullname from biyktinfotb)c,
(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d where 1=1 and orderstates<>'1000000001' and userid=147
and t.cityid=b.cityid(+) and t.yktid=c.yktid(+)标红的就是出问题的地,我不知道(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d 的where t.orderid怎么与tlrepairchargetb 里的orderid关联,求高手指点
(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d
你的这个语句是访问不到你外面设置的别名t的。
你要不修改成
(select k.orderid,k.chargflag from tlrepairchargetb k, v_Tlbankorder j where j.orderid=k.orderid) d
(select orderid,chargflag from tlrepairchargetb where t.orderid=orderid) d
你在里面不可以访问外面的t表,你可以把这个where条件加到外面去,在外面用t表的orderid和tlrepairchargetb的orderid字段进行关联就可以了
where 1=1 and orderstates<>'1000000001' and userid=147
谁能告诉我这里的where 1=1加上去有什么用啊,对oracle常量谓词的用法好迷惑啊