Search code examples
reporting-servicesmicrosoft-dynamicsax

Union in AX static Query - INNER and LEFT Joins are causing errors

I want to make this kind of T-SQL code in AX static query, the issue here is that, when I'm making QueryType: Union instead of Join, I get an error that no Outer Joins can be used, only: exist joins and not exist joins, what can I do about this

SELECT

CUS.CurCode     AS Currency,
PSL.val             AS PayPercent,
/* ... */

FROM CustTrans CUS
INNER JOIN  CustTable ACC           ON CUS.AccountNum       = ACC.AccountNum
LEFT  JOIN  Dimensions DIM          ON CUS.Dimension3_      = DIM.Num
LEFT  JOIN  ProjInvoiceJour PIJ     ON CUS.Voucher          = PIJ.LedgerVoucher

UNION ALL

SELECT
/* ... */
PMT.NumOfMonths         AS Months,
PMT.NumOfDays           AS Days,
PMT.PaymSched           AS PaymSched,
PSL.val                 AS PayPercent,

FROM ProjInvoiceOnAccTrans ONA
INNER JOIN  ProjTable PRO           ON ONA.ProjId               = PRO.ProjId
INNER JOIN  ProjInvoiceTable PRI    ON PRO.ProjInvoiceProjId    = PRI.ProjInvoiceProjId
LEFT  JOIN  PaymTerm PMT            ON PRI.Payment              = PMT.PaymTermId
Solution

  • First create a query for the top part of the Union. So create a query just for this part of the SQL:

    SELECT

CUS.CurCode     AS Currency,
PSL.val             AS PayPercent,
/* ... */

FROM CustTrans CUS
INNER JOIN  CustTable ACC           ON CUS.AccountNum       = ACC.AccountNum
LEFT  JOIN  Dimensions DIM          ON CUS.Dimension3_      = DIM.Num
LEFT  JOIN  ProjInvoiceJour PIJ     ON CUS.Voucher          = PIJ.LedgerVoucher

    Next create a second query for the bottom part of the union. So create a query just for this part of the SQL:

    SELECT
/* ... */
PMT.NumOfMonths         AS Months,
PMT.NumOfDays           AS Days,
PMT.PaymSched           AS PaymSched,
PSL.val                 AS PayPercent,

FROM ProjInvoiceOnAccTrans ONA
INNER JOIN  ProjTable PRO           ON ONA.ProjId               = PRO.ProjId
INNER JOIN  ProjInvoiceTable PRI    ON PRO.ProjInvoiceProjId    = PRI.ProjInvoiceProjId
LEFT  JOIN  PaymTerm PMT            ON PRI.Payment              = PMT.PaymTermId

    Now create a view for each of these queries. Here is a link to an article on how to create a view based on a query: http://msdn.microsoft.com/en-us/library/aa558501.aspx. Then create a third query to do the union. The union query will take the two views you created as its data sources and will union them together.

    Also note that the fields returned from the two views that you are doing the union on need to return the same fields. In your example the two queries that you are doing the union on are not returning the same fields. I am assuming that in reality they return the same fields but you just left some of the fields out in the example to save space. But I thought I would mention it just to be safe.