升级到V6R1 后一些 SQL 无法运行，Error: SQL0338 。

wguxin

在原来的系统上可以执行的（V5R1） ,现在升级到 V6R1 导致一些SQL 无法运行。

Error: SQL0338 - JOIN predicate not valid. (State:S1000, Native Code: FFFFFEAE)

sql :

select  distinct
        pscono company,
        psfaci facility,
        psprno garment,
        mmitds item_name,
        psmtno component_number,
        psitty item_type,
        pscnqt quantity

from    mpdsum ,
        mitmas

left outer join
        mitmah
on      pscono = hmcono
and     psmtno = hmitno

inner join MITWHL
on PSCONO=MWCONO  and PSFACI=MWFACI
inner join MITBAL
on PSCONO=MBCONO and MWWHLO=MBWHLO and psmtno=MBITNO


where   pscono = 100
and     psfaci = 'PAP'
and     psstrt = '001'
and     mmcono = pscono
and     mmitno = psmtno

如果全部改为 join 方式就可以了。like  this :

select  distinct
        pscono company,
        psfaci facility,
        psprno garment,
        mmitds item_name,
        psmtno component_number,
        psitty item_type,
        pscnqt quantity

from    mpdsum
inner join  mitmas
on     mmcono = pscono
and     mmitno = psmtno
left outer join
        mitmah
on      pscono = hmcono
and     psmtno = hmitno

inner join MITWHL
on PSCONO=MWCONO  and PSFACI=MWFACI
inner join MITBAL
on PSCONO=MBCONO and MWWHLO=MBWHLO and psmtno=MBITNO


where   pscono = 100
and     psfaci = 'PAP'
and     psstrt = '001'


这算不算as400 的bug  ? ,  ibm  有关于这个问题的补丁吗 ？

passthru

做过obj版本到v6r1的步骤吗？

wguxin

ibm 文档:

Resolving a SQL0338 failure
In V6R1, a query might fail with SQL0338 for queries with a JOIN. In earlier releases, this usage was
allowed. The error needs to be issued because of the implied order of the join condition. To resolve the
SQL0338 failure, add parentheses to the query to remove the ambiguity.
Select a.c1
From BASE1 a LEFT OUTER JOIN BASE3 c
LEFT OUTER JOIN BASE2 b on a.c1 = b.c1
on b.c1n = c.c1n
is equivalent to
Select a.c1
From BASE1 a LEFT OUTER JOIN (BASE3 c
LEFT OUTER JOIN BASE2 b on a.c1 = b.c1) on b.c1n = c.c1n
SQL0338 can also be issued when a table is specified followed by a comma and a joined table.
Select a.c1
From BASE1 a, BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1
is equivalent to
Select a.c1
From BASE1 a, (BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1)
To specify a.c1 in the ON clause for the joined table, include the first table within the joined table.
Select a.c1
From BASE1 a CROSS JOIN BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1


所有的程序和报表都要检查...................

wguxin

附上ibm 的文档

wguxin

sorry ！不是升级的， 新买的机器：

Model: i570 Power6, 35000CPW

CPU: 8 nos. (activated 7 way / 8 way installed)

Memory: 112G (activated 100G / 112G installed)

Storage: 12TB (96x141.12GB 15krpm HDD)

TS3100 Tape Library (LTO4)

有人知道大概要多少rmb 吗？

kelvenlee

Model: i570 Power6, 35000CPW

CPU: 8 nos. (activated 7 way / 8 way installed)

Memory: 112G (activated 100G / 112G installed)

Storage: 12TB (96x141.12GB 15krpm HDD)

TS3100 Tape Library (LTO4)   

total: 大概900万左右

dave_10

不错 570 595 今后还可以升到power 7

[ 本帖最后由 dave_10 于 2009-8-25 22:13 编辑 ]

5i

哈哈，楼主使用的是MOVEX。
做了7，8年MOVEX，看着亲切啊，不过现在不做MOVEX了。