各位大侠,帮忙看看这段SQL有错吗?~~~ 我弄不明白了!~~ 有不明白的地方可以问我谢谢了!
select 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t - nvl(sum(tc_oob09), 0),
isa08t * oma24 - nvl(sum(tc_oob10), 0),
oma00,
oma55,
oma54t
from (SELECT 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t,
isa08t * oma24,
oma00,
oma55,
oma54t
FROM omab_tmp, isa_file
where isa04 = oma01) a,
(select tc_oob16, nvl(sum(tc_oob09), 0), nvl(sum(tc_oob10), 0)
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01
and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+)
解决方案 »
- 无法启动oracleorahome81managermentserver??请高手指点!
- 在sql server 中如何向异地的oracle 数据库中插入记录
- sql优化的问题,
- Oracle9i.中国it实验室的远程教学ocp录像 bt下载
- 请教!《oracle8初学者指南》中关于联机重做日志的一句话不知如何理解。。。
- 新手的简单问题,orcale高手请进(来者有分)
- 怎样改变已经设为外键的字段的类型呀?
- 我这个触发器为什么在在执行delete操作时不管用?
- 求救!插入远程表出ORA-01008: not all variables bound
- 请问哪里可以找到oracle的错误代码
- 一个查询语句,请各位帮帮忙。
- 这个外键关联查询sql语句应该怎么写?
isa08t * oma24 - nvl(sum(tc_oob10), 0),
里面的nvl(sum(tc_oob09), 0),
和这个 nvl(sum(tc_oob10), 0)需要建立别名使用,不能直接用
a.oma01,
' ',
' ',
'',
a.isa01,
a.oma23,
a.isa08t - b.tc_oob09,
a.isa08toma24 - b.tc_oob10,
a.oma00,
a.oma55,
a.oma54t
from (SELECT
c.oma01 as oma01,
c.isa01 as isa01,
c.oma23 as oma23,
c.isa08t as isa08t,
c.isa08t * oma24 as isa08toma24,
c.oma00 as oma00,
c.oma55 as oma55,
c.oma54t as oma54t
FROM omab_tmp c , isa_file d
where c.isa04 = d.oma01) a, -- omab_tmp 和 isa_file 表结构 不清楚,不晓得这些字段是什么表的。
(select tc_oob16 as tc_oob16, nvl(sum(tc_oob09), 0) as tc_oob09, nvl(sum(tc_oob10), 0) as tc_oob10
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01
and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+)
2、isa08t - nvl(sum(tc_oob09), 0)等没试过,不知道这样写会不会有问题
如果可以,至少不建议这样写。可以改为:在内层先GROUP BY,并对需要SUM的执行计算,赋给别名试试如下:
select 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t - nvl(tc_oob09_sum, 0),
isa08t * oma24 - nvl(tc_oob10_sum, 0),
oma00,
oma55,
oma54t
from (SELECT oma01,isa01,oma23,isa08t,oma24,oma00,oma55,oma54t
FROM omab_tmp, isa_file
where isa04 = oma01) a,
(select tc_oob16, nvl(sum(tc_oob09), 0) tc_oob09_sum, nvl(sum(tc_oob10), 0) tc_oob10_sum
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01 and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+);
这两个列要取一个别名,在外层引用时,分别用别名应该就可以