直接看代码吧:
static void OuterJoinWhere(Args _args)
{
WMSStoreArea wmsStoreArea;
WMSLocation wmsLocation;
;
while select * from wmsStoreArea
outer join wmsLocation
where wmsLocation.storeAreaId == wmsStoreArea.storeAreaId &&
wmsLocation.inventLocationId == 'MW'
{
info(wmsStoreArea.storeAreaId + ' '+wmsLocation.storeAreaId+' '+wmsLocation.inventLocationId);
}
}
上面的语句翻译成SQL语法可能是如下两种情况:{
WMSStoreArea wmsStoreArea;
WMSLocation wmsLocation;
;
while select * from wmsStoreArea
outer join wmsLocation
where wmsLocation.storeAreaId == wmsStoreArea.storeAreaId &&
wmsLocation.inventLocationId == 'MW'
{
info(wmsStoreArea.storeAreaId + ' '+wmsLocation.storeAreaId+' '+wmsLocation.inventLocationId);
}
}
1.Where条件在On后
select a.StoreAreaId,b.InventLocationId from WMSStoreArea a
left join WMSLocation b on a.storeareaId = b.storeareaid
where b.InventLocationId = 'MW'
2.Where条件在ON中
select a.StoreAreaId,b.InventLocationId from WMSStoreArea a
left join WMSLocation b on (a.storeareaId = b.storeareaid and b.InventLocationId = 'MW')
这个也很明显,先执行ON的子查询,子查询的结果再跟WMSStoreArea表外联。left join WMSLocation b on (a.storeareaId = b.storeareaid and b.InventLocationId = 'MW')
这样两者有个区别就是2中保留了null行,而1没有保留null。
那么AX中的语句翻译成其中的哪个那?跟踪一下执行的SQL语句就知道了
用户 ID: Admin
时间: 23:02:01 2008-3-7
版本: Microsoft Dynamics 4.0 (内部版本号 2501.116)
数据库: Microsoft SQL Server
SQL 语句: SELECT A.STOREAREAID,A.NAME,A.STOREAREATYPE,A.INVENTORYSTATUS,A.RECVERSION,A.RECID,B.POSITION,B.LEVEL_,B.RACK,B.INVENTLOCATIONID,B.WMSLOCATIONID,B.CHECKTEXT,B.SORTCODE,B.MANUALSORTCODE,B.MANUALNAME,B.AISLEID,B.LOCATIONTYPE,B.HEIGHT,B.WIDTH,B.DEPTH,B.VOLUME,B.PALLETTYPEGROUPID,B.STOREAREAID,B.MAXPALLETCOUNT,B.INPUTLOCATION,B.INPUTBLOCKINGCAUSEID,B.OUTPUTBLOCKINGCAUSEID,B.PICKINGAREAID,B.ABSOLUTEHEIGHT,B.STOREZONEID,B.INVENTORYSTATUS,B.GOODBAD,B.SAFEPACKERQTY,B.MAXPACKERSQTY,B.ITEMID,B.RECVERSION,B.RECID FROM {oj WMSSTOREAREA A LEFT OUTER JOIN WMSLOCATION B ON ((B.DATAAREAID=?) AND ((A.STOREAREAID=B.STOREAREAID) AND (B.INVENTLOCATIONID=?)))} WHERE (A.DATAAREAID=?) [ID=3380, 已重用=是]
至于在X++中如何写出能翻译成第一种SQL的语句,那就不得而知了。