Oct 6, 2016

How to difference 'Join' between AX (X++) and MS SQL (T-SQL) (X++과 T-SQL의 조인 비교)

AX (X++) MS SQL(T-SQL)
join CROSS JOIN
CustTable _CustTable;
CustGroup _CustGroup;

select AccountNum from __CustTable
    join TaxGroupId from __CustGroup
    where __CustGroup._CustGroup == __CustTable._CustGroup;		 SELECT T1.ACCOUNTNUM, T1.RECID, T2.TAXGROUPID, T2.RECID
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
  AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
   AND (T2.CUSTGROUP=T1.CUSTGROUP))
outer join LEFT OUTER JOIN
CustTable _CustTable;
CustBankAccount _CustBankAccount;

select AccountNum from _CustTable
    outer join AccountID from _CustBankAccount
    where _CustBankAccount.CustAccount == _CustTable.AccountNum;		 SELECT T1.ACCOUNTNUM, T1.RECID, T2.ACCOUNTID, T2.RECID
FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2
ON (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT))
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
exists join EXISTS (SELECT 'x'…)
CustTable _CustTable;
CustBankAccount _CustBankAccount;

select AccountNum from _CustBankAccount
    exists join _CustTable
    where _CustBankAccount.CustAccount == _CustTable.AccountNum;		 SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
              AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM)))
not exists join NOT (EXISTS (SELECT 'x'…))
CustTable _CustTable;
CustBankAccount _CustBankAccount;

select AccountNum from _CustBankAccount
    notexists join _CustTable
    where _CustBankAccount.CustAccount == _CustTable.AccountNum;		 SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND NOT (EXISTS (SELECT 'x'
                 FROM CUSTTABLE T2
                 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
                   AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM))))
join after(다음) exists join EXISTS (SELECT 'x'… CROSS JOIN…)
CustTable _CustTable;
CustGroup _CustGroup;
CustBankAccount _CustBankAccount;

select AccountNum from _CustBankAccount
    exists join _CustTable
    where _CustBankAccount.CustAccount == _CustTable.AccountNum
    join TaxGroupId from _CustGroup
    where _CustGroup._CustGroup == _CustTable._CustGroup;		 SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2 CROSS JOIN CUSTGROUP T3
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM))
              AND (((T3.PARTITION=?) AND (T3.DATAAREAID=?))
               AND (T3.CUSTGROUP=T2.CUSTGROUP)))
exists join after(다음) exists join EXISTS (SELECT 'x'… EXISTS (SELECT 'x'…))
CustTable _CustTable;
CustGroup _CustGroup;
CustBankAccount _CustBankAccount;

select AccountNum from _CustBankAccount
    exists join _CustTable
    where _CustBankAccount.CustAccount == _CustTable.AccountNum
    exists join _CustGroup
    where _CustGroup._CustGroup  == _CustTable._CustGroup
       && _CustGroup.TaxGroupId == "Your tax group";		 SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

AND EXISTS (SELECT 'x' FROM CUSTTABLE T2
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM))
               AND EXISTS (SELECT 'x'
                           FROM CUSTGROUP T3
                           WHERE (((T3.PARTITION=?) AND (T3.DATAAREAID=?))
                              AND ((T3.CUSTGROUP=T2.CUSTGROUP) AND (T3.TAXGROUPID=?)))))
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)