同样的sql在不同的DB里执行产生的疑问？
有两个INFORMIXSERVER：test1和test2,分别在不同的主机上。test1版本：IDS11.10FC2
test2版本：IDS9.40FC7
配置ODBC，连接到test2里，执行如下sql，正常执行，无语法错误：
SELECT DISTINCT
J.ZX02,
a.pmm01,
a.pmm02,
a.pmm03,
a.pmm04,
a.pmm05,
h.pmme20 as pmm07,
a.pmm09,
a.pmm13,
a.pmmgrup,
a.pmm21,
a.pmm41,
a.pmm26,
a.pmm10,
a.pmm11,
a.pmm12,
a.pmm17,
a.pmm20,
a.pmm22,
a.pmm40,
a.pmm42,
a.pmm43,
b.gem02,
d.pma02,
e.oah02,
h.pmme04,
h.pmme10,
h.pmme11,
h.pmme12,
g.pmc03,
g.pmc14,
k.gec02,
trim(g.pmc081)||trim(NVL(g.pmc082,' ')) pmc081,
trim(g.pmc091)||trim(NVL(g.pmc092,' '))||trim(NVL(g.pmc093,' ')) pmc091,
trim(NVL(x.pme031,' '))||trim(NVL(x.pme032,' '))||trim(NVL(x.pme033,' '))||trim(NVL(x.pme034,' '))||trim(NVL(x.pme035,' ')) pme031,
trim(NVL(x.pme041,' '))||trim(NVL(x.pme042,' '))pme041
FROM
xyz@test2:pmm_file a
LEFT JOIN xyz@test2:gem_file bON a.pmm13=b.gem01
LEFT JOIN xyz@test2:pma_file dON a.pmm20=d.pma01
LEFT JOIN xyz@test2:oah_file eON a.pmm41=e.oah01
LEFT JOIN xyz@test2:pmme_file h ON a.pmm01=h.pmme01
LEFT JOIN xyz@test2:pmc_file gON a.pmm09=g.pmc01
LEFT JOIN xyz@test2:zx_file j ON a.pmm12=j.zx01
LEFT JOIN xyz@test2:gec_file kON a.pmm21=k.gec01
LEFT JOIN xyz@test2:pme_file xON a.pmm10=x.pme01
WHERE
a.pmm01 like 'B40133'
但是将同样的sql运行在同样用ODBC连接的test1（test1和test2有信任关系，且权限正确)里面时，报语法错误。
然后将sql换一种写法，sql如下（蓝色表示不同的地方):
SELECT AA.*,J.ZX02
FROM(
SELECT DISTINCT
a.pmm01,
a.pmm02,
a.pmm03,
a.pmm04,
a.pmm05,
h.pmme20 as pmm07,
a.pmm09,
a.pmm13,
a.pmmgrup,
a.pmm21,
a.pmm41,
a.pmm26,
a.pmm10,
a.pmm11,
a.pmm12,
a.pmm17,
a.pmm20,
a.pmm22,
a.pmm40,
a.pmm42,
a.pmm43,
b.gem02,
d.pma02,
e.oah02,
h.pmme04,
h.pmme10,
h.pmme11,
h.pmme12,
g.pmc03,
g.pmc14,
k.gec02,
trim(g.pmc081)||trim(NVL(g.pmc082,' ')) pmc081,
trim(g.pmc091)||trim(NVL(g.pmc092,' '))||trim(NVL(g.pmc093,' ')) pmc091,
trim(NVL(x.pme031,' '))||trim(NVL(x.pme032,' '))||trim(NVL(x.pme033,' '))||trim(NVL(x.pme034,' '))||trim(NVL(x.pme035,' ')) pme031,
trim(NVL(x.pme041,' '))||trim(NVL(x.pme042,' '))pme041
FROM
xyz@test2:pmm_file a
LEFT JOIN xyz@test2:gem_file bON a.pmm13=b.gem01
LEFT JOIN xyz@test2:pma_file dON a.pmm20=d.pma01
LEFT JOIN xyz@test2:oah_file eON a.pmm41=e.oah01
LEFT JOIN xyz@test2:pmme_file h ON a.pmm01=h.pmme01
LEFT JOIN xyz@test2:pmc_file gON a.pmm09=g.pmc01
LEFT JOIN xyz@test2:gec_file kON a.pmm21=k.gec01
LEFT JOIN xyz@test2:pme_file xON a.pmm10=x.pme01
WHERE
a.pmm01 like 'B40133'
) AA
LEFT JOIN xyz@test2:zx_file j ON AA.pmm12=j.zx01
以上sql运行在同样用ODBC连接的test1（test1和test2有信任关系，且权限正确)里面时，又是正常的，
运行在用ODBC连接的test2时报语法错误。
比较困惑，按理说都是访问test2上的xyz资料库，应该与test1没有什么关系，难道与数据库版本有关？ 有可能，IDS不同版本的SQL并不是完全向下兼容的。仔细查查手册吧。
页:
[1]