You’re offline. This is a read only version of the page.
155
Hi everyone ,
the query works correctly. Below code is from x++ debugger ax2012 R3 :
SELECT RecId, AccountNum, Voucher, TransDate, CurrencyCode, AmountCur, AmountMST, ExchRate
FROM VendTrans(VendTrans_1)
WHERE ((TransType = 15))
AND ((TransDate>={ts '2015-04-01 00:00:00.000'}
AND TransDate<={ts '2015-12-31 00:00:00.000'}))
JOIN OffsetTransVoucher, SettleAmountCur, SettleAmountMST, SettlementVoucher
FROM VendSettlement(VendSettlement_1)
ON VendTrans.Voucher = VendSettlement.OffsetTransVoucher
AND VendTrans.RecId = VendSettlement.OffsetRecid
JOIN Invoice, Voucher, TransDate, CurrencyCode, AmountCur, AmountMST, SettleAmountCur, SettleAmountMST, ExchRate, VendExchAdjustmentRealized, TransType
FROM VendTrans(VendTrans_1_1)
ON VendSettlement.TransRecId = VendTrans.RecId
OUTER JOIN VendExchAdjustmentRealized
FROM VendTrans(VendTrans_1_2)
ON VendSettlement.SettlementVoucher = VendTrans.Voucher
AND ((NOT (VendExchAdjustmentRealized = 0)))
Can anyone convert into T-SQL. (SSMS 2014)
Thank you!
*This post is locked for comments
You need to add dataAreaId on all joins like below.
VendTrans_1.DataAreaId = VendSettlement_1.DataAreaId
vendtrans_1_2 has left outer join so "" may still work, but not for others. You can now play with the query and refine as you have the basic working structure.
good catch.. that's what Vladislav can add during the review/testing.
I don't see partition and DataAreaId fields in your T-SQL code, therefore it's incorrect. It's not the same thing as original query.
Yes, querying AX database directly is more difficult than people think and they often do it wrong. You should always thing whether you want to do it in the first place.
Hi Vladislav,
It would be something like below.. you can review and make adjustments to make sure data is not getting duplicated etc.
SELECT VendTrans_1.RecId, VendTrans_1.AccountNum, VendTrans_1.Voucher, VendTrans_1.TransDate, VendTrans_1.CurrencyCode, VendTrans_1.AmountCur, VendTrans_1.AmountMST, VendTrans_1.ExchRate ,
VendSettlement_1.OffsetTransVoucher, VendSettlement_1.SettleAmountCur, VendSettlement_1.SettleAmountMST, VendSettlement_1.SettlementVoucher, --vendsettlement
VendTrans_1_1.Invoice, VendTrans_1_1.Voucher, VendTrans_1_1.TransDate, VendTrans_1_1.CurrencyCode, VendTrans_1_1.AmountCur, VendTrans_1_1.AmountMST, VendTrans_1_1.SettleAmountCur,
VendTrans_1_1.SettleAmountMST, VendTrans_1_1.ExchRate, VendTrans_1_1.VendExchAdjustmentRealized, VendTrans_1_1.TransType -- vendtrans
,VendTrans_1_2.VendExchAdjustmentRealized
FROM VendTrans VendTrans_1
inner JOIN
VendSettlement VendSettlement_1
ON VendTrans_1.Voucher = VendSettlement_1.OffsetTransVoucher
AND VendTrans_1.RecId = VendSettlement_1.OffsetRecid
and ((TransType = 15))
AND ((VendTrans_1.TransDate>= '2015-04-01 00:00:00.000'
AND VendTrans_1.TransDate <= '2015-12-31 00:00:00.000'))
inner JOIN
VendTrans VendTrans_1_1
ON VendSettlement_1.TransRecId = VendTrans_1_1.RecId
left join VendTrans VendTrans_1_2
ON VendSettlement_1.SettlementVoucher = VendTrans_1_2.Voucher
AND VendTrans_1_2.VendExchAdjustmentRealized != 0
Thanks,
Satish Panwar
Please take time to click 'Yes' against the answers that help you guide in right direction to help other community members.
It should be quite easy - try it and ask if you run into something that you don't understand. Just don't forget that the pseudo-SQL code above doesn't include data area IDs and partition IDs, because that's normally handled by AX kernel, but you must do it if you query the DB directly.
An alternative approach is running the query and fetching the actual T-SQL query in the database (e.g. with extended events).
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (