select count(0)
from sysadm.f2dd_PARTS_REQ a
left outer join sysadm.f2dd_FIRM_ORDER_PERIOD e
on e.FACTORY_CD = a.FACTORY_CD
and e.PLAN_ID=(select MAX(GKDP_PLAN_ID)
from sysadm.f2dd_PLAN_NO_CTL
where LATEST_PLAN_FLG = '1')
上面的sql在db2中执行出现错误：
select count(0) from sysadm.f2dd_PARTS_REQ a left outer join sysadm.f2dd_FIRM_ORDER_PERIOD e on e.FACTORY_CD = a.FACTORY_CD and e.PLAN_ID=(select MAX(GKDP_PLAN_ID) from sysadm.f2dd_PLAN_NO_CTL where LATEST_PLAN_FLG = '1')
SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement
is not valid. SQLSTATE=42972
SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid.
Explanation:
An ON clause associated with a JOIN operator or in a MERGE
statement is not valid for one of the following reasons.
o The ON clause cannot include any subqueries.
o Column references in an ON clause must only reference columns
of tables that are in the scope of the ON clause.
o Scalar fullselects are not allowed in the expressions of an
ON clause.
o A function referenced in an ON clause of a full outer join
must be deterministic and have no external action.
o A dereference operation (->) cannot be used.
o A SQL function or SQL method cannot be used.
The statement cannot be processed.
User Response:
Correct the ON clause to reference appropriate columns or delete
any subqueries or scalar fullselects. Remove any dereference
operations, SQL functions, or SQL methods from the ON clause.
If using full outer join ensure that all functions in the ON
clause are deterministic and have no external action.
sqlcode : -338
sqlstate : 42972
请各位大侠帮忙，如何解决这个问题。
------解决方案--------------------------------------------------------
把你的列明前，加上所属表。
例如:LATEST_PLAN_FLG = '1'变成 ***.LATEST_PLAN_FLG = '1'
------解决方案--------------------------------------------------------
这个是一个逻辑错误，你应该把
e.PLAN_ID=(select MAX(GKDP_PLAN_ID)
from sysadm.f2dd_PLAN_NO_CTL
where LATEST_PLAN_FLG = '1')
作为整个left join的where条件，这个语句改写为下面的写法：
select count(0)
from sysadm.f2dd_PARTS_REQ a
left outer join sysadm.f2dd_FIRM_ORDER_PERIOD e
on e.FACTORY_CD = a.FACTORY_CD
where e.PLAN_ID=(select MAX(GKDP_PLAN_ID)
from sysadm.f2dd_PLAN_NO_CTL
where LATEST_PLAN_FLG = '1')