Langsung ke konten utama

Joins in X++ into T-SQL

1. join in X++:
select AccountNum from custTable
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;
CROSS JOIN in T-SQL:
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))

2. outer join in X++:
select AccountNum from custTable
    outer join AccountID from custBankAccount
    where custBankAccount.CustAccount == custTable.AccountNum;
LEFT OUTER JOIN in T-SQL:
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=?))

3. exists join in X++:
select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;
EXISTS (SELECT 'x'...) in T-SQL:
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)))

4. notexists join in X++:
select AccountNum from custBankAccount
    notexists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;
NOT (EXISTS (SELECT 'x'...)) in T-SQL:
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))))

5. join after exists join in X++:
select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;
EXISTS (SELECT 'x'...CROSS JOIN...) in T-SQL:
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)))

6. exists join after exists join in X++:
select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    exists join custGroup
    where custGroup.CustGroup  == custTable.CustGroup
       && custGroup.TaxGroupId == "Std";
EXISTS (SELECT 'x'... EXISTS (SELECT 'x'...)) in T-SQL:
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=?)))))

Conclusion
each exists join is considered as a separate statement, but any join after exists joinis coupled or nested.
Label:

Komentar

Posting Komentar

Postingan populer dari blog ini

Use X++ wildcard (LIKE and NOT LIKE) in X++ select statement

For x++ select statements:  select firstOnly batchHistory      where batchHistory.Caption  LIKE  "*Test*"  For x++ queries:  queryBuildRange.value(*Test*); Note the LIKE instead of a '==' and the wildcards inside of the quotations. All other combinations of wildcard usage will work here. This is the same functionality as what the users would put in the grid filtering(eg. '*TEST*' in the caption field filter on the batch tasks form).  However, if you want to find all Captions that do not have the word Test in them (NOT LIKE, !LIKE), you will have to modify the above example slightly.  For x++ select statements:  select firstOnly batchHistory      where  !( batchHistory.Caption LIKE "*TEST*" ) ;  For x++ queries:  queryBuildRange.value(!*Test*);
Posting Komentar
Baca selengkapnya

Format Label Tom Jerry (MS Word)

Setelah beberapa lama aku tidak membuat label undangan, tiba-tiba kemarin aku diminta untuk membuat label undangan Wisuda dengan menggunakan label merek Tom & Jerry (TJ). Harusnya sih menjadi tugas sekretaris panitia tapi karena yang bersangkutan ada tugas di luar kantor maka aku yang membereskannya. Mula-mula aku membuat format labelnya dengan cara manual, tapi untung ada pak Mulyadi (staf tata usaha) yang menyarankan aku untuk mengunduh format label yang telah ada di Internet. Mulailah aku browsing di google dan menemukan format label berbagai ukuran dari website Tom & Jerry di alamat berikut  http://tjlabels.com/en/download.html  (yang link ini agak rempong krn pake masukin nomor captcha-captcha-an). Sedangkan kalau yang link ini langsung pilih file ga pake nomor captcha-captcha-an ( http://tjlabels.com/assets/download/ ). Ternyata juga ada banyak berbagai blog yang menyediakan  link download seperti ini, tapi aku mem- posting  artikel ini ...
Posting Komentar
Baca selengkapnya

Cleanup Jobs AX 2012

Cleanup jobs in Dynamics AX 2012 can help delete old data that is not needed anymore, and this reduction in database size can improve performance. In Dynamics AX 2012, cleanup jobs are available across various modules to run manually or in a batch mode. It is important to note that these jobs should be run after enough analysis determines that the business doesn’t need this data in the future. Please be sure to discuss with business management before deleting old data used in transactions that is not required any longer. Dynamics AX 2012 adds data to various tables for staging/intermediate purpose and this staging/intermediate data can be deleted once the transaction has been posted such as Sales update history, Purchase update history data. Here is the list of cleanup jobs available in Dynamics AX 2012 along with brief descriptions. All of these cleanup jobs are available in module > Periodic > Cleanup section Module Job Description General Ledger Clean up...
Posting Komentar
Baca selengkapnya