Hello everybody! I’m a functional consultant with a little knowledge of SQL and I’m trying to apply two instructions in AX, but I don’t know the sintax. Hope someone can help me. They are intended to get data for a report.
Many thanks!
ISSLibroCompras
SELECT DISTINCT
A.PURCHID, A.INVOICEID, A.ISS_TIPOCOMPRAVENTA, A.DATAAREAID, B.DATAAREAID AS DATAAREAID#2, C.DATAAREAID AS DATAAREAID#3, C.VATNUM, C.ISSVERIFIERDIGIT, C.NAME, D.DATAAREAID AS DATAAREAID#4, D.INVOICEDATE, D.INVOICEAMOUNT, D.SUMTAX, E.DATAAREAID AS DATAAREAID#5, E.TYPE
FROM dbo.PURCHLINE AS A INNER JOIN
dbo.PURCHTABLE AS B ON A.PURCHID = B.PURCHID AND A.DATAAREAID = B.DATAAREAID INNER JOIN
dbo.VENDTABLE AS C ON B.INVOICEACCOUNT = C.ACCOUNTNUM AND B.DATAAREAID = C.DATAAREAID INNER JOIN
dbo.VENDINVOICEJOUR AS D ON A.PURCHID = D.PURCHID AND A.DATAAREAID = D.DATAAREAID AND A.INVOICEID = D.INVOICEID INNER JOIN
dbo.DIRPARTYTABLE AS E ON C.PARTYID = E.PARTYID AND C.DATAAREAID = E.DATAAREAID
WHERE (NOT (B.ISS_CAJACHICA = 1))
ORDER BY A.PURCHID, A.INVOICEID
ISSLibroComprasCC
SELECT A.INVOICEID, A.TRANSDATE AS INVOICEDATE, A.ISS_TIPOCOMPRAVENTA, SUM(A.LINEAMOUNT) AS INVOICEAMOUNT, A.TAXGROUP,
A.DATAAREAID, B.DATAAREAID AS DATAAREAID#2, B.PURCHID, C.DATAAREAID AS DATAAREAID#3, C.VATNUM, C.ISSVERIFIERDIGIT, C.NAME,
D.DATAAREAID AS DATAAREAID#4, D.TYPE
FROM dbo.PURCHLINE AS A INNER JOIN
dbo.PURCHTABLE AS B ON A.PURCHID = B.PURCHID AND A.DATAAREAID = B.DATAAREAID INNER JOIN
dbo.VENDTABLE AS C ON A.VENDINVOICEACCOUNT = C.ACCOUNTNUM AND A.DATAAREAID = C.DATAAREAID INNER JOIN
dbo.DIRPARTYTABLE AS D ON C.PARTYID = D.PARTYID AND C.DATAAREAID = D.DATAAREAID
WHERE (B.ISS_CAJACHICA = 1)
GROUP BY A.INVOICEID, A.TRANSDATE, A.ISS_TIPOCOMPRAVENTA, A.TAXGROUP, A.DATAAREAID, B.DATAAREAID, B.PURCHID, C.DATAAREAID, C.VATNUM,
C.ISSVERIFIERDIGIT, C.NAME, D.DATAAREAID, D.TYPE
ORDER BY A.INVOICEID, B.PURCHID