Dynamics Community Forum Thread Details

SQL operator support (CROSS APPLY)

Hello:

I haven't found anything searching on my own. I'm wondering if X++ Query, SQL, or QueryBuilder support the CROSS APPLY SQL operator in some manner. The issue I have is I need a value from SALESTABLE but going through CUSTINVOICETRANS give me too many rows. I got the below SQL working within SSMS, but wondering how to translate the equivalent in an AOT Query, X++ SQL, or QueryBuilder Initially, it seems to make sense to use QueryBuilder to achieve what I need, but I'm open to other options if I can do it all without too much fuss (if that makes sense). Thank you!

SELECT
T1.ACCOUNTNUM,
T12.STREET,
T12.CITY,
T12.STATE AS 'STATE',
CASE WHEN DETAILS.[CUSTOM FIELD] <> '' THEN DETAILS.[CUSTOM FIELD]
ELSE T2.INVOICE
END AS 'INVOICE NUMBER',
FROM CUSTTRANSOPEN T1
   JOIN CUSTTRANS T2
   ON T2.ACCOUNTNUM = T1.ACCOUNTNUM
   AND T1.REFRECID = T2.RECID
       JOIN CUSTTABLE T3
       ON T3.ACCOUNTNUM = T2.ACCOUNTNUM
           JOIN DIRPARTYTABLE T10
           ON T10.RECID = T3.PARTY
               JOIN LOGISTICSLOCATION T11
               ON T11.RECID = T10.PRIMARYADDRESSLOCATION
                   JOIN LOGISTICSPOSTALADDRESS T12
                   ON T12.LOCATION = T11.RECID

       CROSS APPLY
       (
           SELECT TOP 1 T4.SALESID, T5.LINENUM FROM
           CUSTINVOICEJOUR T4
               JOIN CUSTINVOICETRANS T5
               ON T5.SALESID = T4.SALESID
               AND T5.INVOICEID = T4.INVOICEID
               AND T5.INVOICEDATE = T4.INVOICEDATE
               AND T5.NUMBERSEQUENCEGROUP = T4.NUMBERSEQUENCEGROUP
                   JOIN SALESTABLE T13
                   ON T13.SALESID = T5.SALESID
           WHERE
               ...
       ) DETAILS
WHERE
...

Categories:

Dynamics 365 Finance

Quick Links