关于union的奇怪问题,高手请进!

[hutulaodao]

我用union报错
ORA-00600: 内部错误代码,自变量: [5213], [], [], [], [], [], [], []
而用union all 却没问题,这是为何?
我的问题是要用union 而非union all

[bzszp]

内部错误
需要技术支持来解决

[seafer]

请把语句贴一下，大家好分析

[hutulaodao]

select a.huoh,round(sum(nvl(a.shul,0)-nvl(b.shul,0)),2) shul,
round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine,
decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj
from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where zhudcwh like '00%' and
(beidcwh between '010001' and '010020' or beidcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) a,
( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where beidcwh like '00%' and
(zhudcwh between '010001' and '010020' or zhudcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) b
where a.huoh=b.huoh(+)
group by a.huoh
union all
select b.huoh,round(sum(nvl(a.shul,0)-nvl(b.shul,0)),2) shul,
round(sum(nvl(a.jine,0)-nvl(b.jine,0)),2) jine,
decode(sum(nvl(a.shul,0)-nvl(b.shul,0)),0,0,round(sum(nvl(a.jine,0)-nvl(b.jine,0))/sum(nvl(a.shul,0)-nvl(b.shul,0)),2)) danj
from (select huoh ,nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where zhudcwh like '00%' and
(beidcwh between '010001' and '010020' or beidcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) a,
( select huoh, nvl(sum(shul), 0) shul, nvl(sum(round(shul * zhixj, 2)), 0) jine
from xs.cangkmxlsz
where beidcwh like '00%' and
(zhudcwh between '010001' and '010020' or zhudcwh > '010030') and
denglrq between to_date('2001.1.1', 'yyyy-mm-dd') and
to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05' and
caozlx = '01' and huoh like '00%' group by huoh) b
where a.huoh(+)=b.huoh
group by b.huoh
其中一条语句大概有两千多行,一条有五十左右

[onejune4450]

回避一下union ，试下面的结果：

select huoh,round(sum(nvl(shul_a,0)-nvl(shul_b,0)),2) shul,
round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine,
decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0,
round(sum(nvl(jine_a,0)-nvl(jine_b,0))
/sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj
(select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
from xs.cangkmxlsz
where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd')
and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05'
and caozlx = '01' and huoh like '00%')
and ((zhudcwh like '00%'
and (beidcwh between '010001' and '010020' or beidcwh > '010030'))
or
(beidcwh like '00%'
and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')))
) group by huoh

[onejune4450]

select huoh,round(sum(nvl(shul_a,0)-nvl(shul_b,0)),2) shul,
round(sum(nvl(jine_a,0)-nvl(jine_b,0)),2) jine,
decode(sum(nvl(shul_a,0)-nvl(shul_b,0)),0,0,
round(sum(nvl(jine_a,0)-nvl(jine_b,0))
/sum(nvl(shul_a,0)-nvl(shul_b,0)),2)) danj
from
(select huoh,decode(substr(zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
from xs.cangkmxlsz
where (denglrq between to_date('2001.1.1', 'yyyy-mm-dd')
and to_date('2001.2.1', 'yyyy-mm-dd') and danjlx = '05'
and caozlx = '01' and huoh like '00%')
and ((zhudcwh like '00%'
and (beidcwh between '010001' and '010020' or beidcwh > '010030'))
or
(beidcwh like '00%'
and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')))
) group by huoh

[hutulaodao]

to onejune4450(中文字符) :你的条件有问题,
decode (substr( zhudcwh,1,2),'00',shul,0) shul_a,
decode(substr(zhudcwh,1,2),'00',zhixj * shul,0) jine_a,
decode(substr(beidcwh,1,2),'00',shul,0) shul_b,
decode(substr(beidcwh,1,2),'00',zhixj * shul,0) jine_b
这四个条件不符合,取出的字段shul不仅与zhudcwh有关,而且与beidcwh有关,一个条件并不能取得正确的shul.

[onejune4450]

beidcwh like '00%' and zhudcwh between '010001' and '010020' or zhudcwh > '010030'
条件决定了
beidcwh like '00%'成立 zhudcwh 就不是‘00%'了,字符类型‘00100001' < '01'

[hutulaodao]

to onejune4450(中文字符) :不懂你的意思,我的条件是
beidcwh like '00%' and (zhudcwh between '010001' and '010020' or zhudcwh > '010030')
beidcwh与zhudcwh不存在谁能决定谁,他们相互独立

[onejune4450]

你用查询结果验证一下。

[hutulaodao]

我还有一个条件是
zhudcwh like '00%' and (beidcwh between '010021' and '010030')
根据一个条件如何区分他们?
结果很多记录,不好验证.

[onejune4450]

我看不出有什么不对。
你可以导出来几个huoh放在一个测试表中试一下。如有问题再来，自己琢磨一下。

[hutulaodao]

我实在太笨了,你说的是对的,谢谢!