You’re offline. This is a read only version of the page.
485
Hello Everyone, I need some help with the following query.....
while select * FROM custInvoiceJour JOIN Invoice, AccountNum,TransDate,Voucher from custtrans
WHERE custInvoiceJour.InvoiceId == custtrans.Invoice && custInvoiceJour.InvoiceAccount==custtrans.AccountNum
&& custInvoiceJour.InvoiceDate==custtrans.TransDate && custInvoiceJour.LedgerVoucher==custtrans.Voucher JOIN
TransCompany,TransRecId,AccountNum from custSettlement WHERE custSettlement.DueDate == custInvoiceJour.DueDate
It does not give me any error but am not getting any result....
This is the SQL equivalent
SELECT * FROM [CUSTINVOICEJOUR] join[CUSTTRANS] ON CUSTTRANS.INVOICE=CUSTINVOICEJOUR.INVOICEID AND
[CUSTTRANS].AccountNum = [CUSTINVOICEJOUR].InvoiceAccount and [CUSTTRANS].TransDate = [CUSTINVOICEJOUR].InvoiceDate
and [CUSTTRANS].Voucher = [CUSTINVOICEJOUR].LedgerVoucher join CUSTSETTLEMENT
on CUSTSETTLEMENT.TransCompany = [CUSTTRANS].dataAreaId
and CUSTSETTLEMENT.TransRecId = [CUSTTRANS].RecId
and CUSTSETTLEMENT.AccountNum = [CUSTTRANS].AccountNum
WHERE [CUSTSETTLEMENT].DUEDATE = [CUSTINVOICEJOUR].DUEDATE
AND [CUSTSETTLEMENT].DUEDATE between'2017-10-01' AND '2018-09-30'
AND [CUSTSETTLEMENT].TRANSDATE <'2018-10-01 0:0:0'
AND [CUSTINVOICEJOUR].INVOICEID LIKE 'ACP%'
AND [CUSTINVOICEJOUR].INVOICEACCOUNT='100318M'
The text in red is was not applied to the X++ code as this can be achieved by anyone who will be running the report.
*This post is locked for comments
We're trying to point out that your SQL query is probably incorrect, because it ignores partitions and legal entities.
Or is your intention to look for invoice IDs starting with ACP in any company? For example, if I work in India and somebody our Brazilian subsidiary has an invoice with ID ACPD01, your SQL query will return it. Is it really what you want? If so, you'll have to use a cross-company query in AX, because AX automatically apply filters and gives you data for your partition and data area.
After you fix these bugs, fix also the other problem I mentioned - you said you wanted the same query but you don't have the same WHERE conditions.
ok, understood. sorry for the misunderstanding. I showed my SQL code because i used it and got the data i want but each time a user wants the info i have to go to the DB and run the sql query. This is what am trying to avoid doing instead, I want to be able to allow the user run a report in AX.
What i really need help with is learn how to write a query in X++ to get back the same result i would get when i run the query in SQL server.
You got me wrong. What I showed you is what SQL code *your* X++ code generates. It's not X++ code (you shouldn't try to run it in AX) and it's nothing I wrote by hand. The static number is a partition ID added by AX when converting your X++ code to SQL code.
You don't need to add ranges for dataAreaId or partition in x++, they are added by the system for you.
If you get errors we can only help if you share them. I'm sure you understand. But you should not try to use the T-SQL statement in x++ since the x++ select syntax is different.
I also recommend that you create a job for developing your query. Start by selecting from one table. When that works, join the second table and finally the third one.
Thanks for your help Martin, will definitely take your recommendation in consideration next time...
I am however a bit lost by the AX query. When I entered this query in my class It gave me error. Why did you use static number(5637144576) in the query? here is the class method to insert data in my table....
Share
Report
All responses (
Answers (
private void insertData()
{
CustInvoiceJour custInvoiceJour;
CustSettlement custSettlement;
CustTrans custtrans;
SELECT * FROM CUSTINVOICEJOUR
JOIN CUSTTRANS
JOIN CUSTSETTLEMENT
WHERE ((CUSTINVOICEJOUR.PARTITION=5637144576)
AND (CUSTINVOICEJOUR.DATAAREAID=N'usmf'))
AND (((CUSTTRANS.PARTITION=5637144576)
AND (CUSTTRANS.DATAAREAID=N'usmf'))
AND ((((CUSTINVOICEJOUR.INVOICEID=CUSTTRANS.INVOICE)
AND (CUSTINVOICEJOUR.INVOICEACCOUNT=CUSTTRANS.ACCOUNTNUM))
AND (CUSTINVOICEJOUR.INVOICEDATE=CUSTTRANS.TRANSDATE))
AND (CUSTINVOICEJOUR.LEDGERVOUCHER=CUSTTRANS.VOUCHER)))
AND (((CUSTSETTLEMENT.PARTITION=5637144576)
AND (CUSTSETTLEMENT.DATAAREAID=N'usmf'))
AND (CUSTSETTLEMENT.DUEDATE=CUSTINVOICEJOUR.DUEDATE))
{
averagedaystopay.clear();
averagedaystopay.InvoiceId = custInvoiceJour.InvoiceId;
averagedaystopay.InvoiceAccount = custInvoiceJour.InvoiceAccount;
averagedaystopay.InvoicingName = custInvoiceJour.InvoicingName;
averagedaystopay.DueDate = custSettlement.DueDate;
averagedaystopay.TransDate = custSettlement.TransDate;
averagedaystopay.SettleAmountReporting = custSettlement.SettleAmountReporting;
averagedaystopay.Payment = custinvoicejour.Payment;
averagedaystopay.InvoiceAmountMST = custInvoiceJour.InvoiceAmountMST;
averagedaystopay.insert();
}
So i have used your query and it gave me errors.
Am trying to build a report in AX 2012 for my users. I tried using the query method but it did not give me much flexibility so
I could not use all 3 table and join them and arrange the date how i wanted , hence I am now trying the RDP way for doing the report.
I appreciate any help possible.
Now you should check whether the query your X++ code generates matches your SQL query. It's immediately clear that it doesn't, because your SQL code doesn't filter by partition and data area ID and your X++ code doesn't have ranges you have in SQL. Therefore you're compare two very different queries.
For completeness, the query generated from AX looks like this:
SELECT * FROM CUSTINVOICEJOUR T1 CROSS JOIN CUSTTRANS T2 CROSS JOIN CUSTSETTLEMENT T3 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'usmf')) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'usmf')) AND ((((T1.INVOICEID=T2.INVOICE) AND (T1.INVOICEACCOUNT=T2.ACCOUNTNUM)) AND (T1.INVOICEDATE=T2.TRANSDATE)) AND (T1.LEDGERVOUCHER=T2.VOUCHER))) AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'usmf')) AND (T3.DUEDATE=T1.DUEDATE))
First of all, let's make your code readable. Next time, please use the </> button in the rich formatting view to paste source code.
X++:
while select custInvoiceJour
join Invoice, AccountNum, TransDate, Voucher from custTrans
where custInvoiceJour.InvoiceId == custTrans.Invoice
&& custInvoiceJour.InvoiceAccount==custTrans.AccountNum
&& custInvoiceJour.InvoiceDate==custTrans.TransDate
&& custInvoiceJour.LedgerVoucher==custTrans.Voucher
join TransCompany, TransRecId, AccountNum from custSettlement
where custSettlement.DueDate == custInvoiceJour.DueDate
SQL:
SELECT * FROM [CUSTINVOICEJOUR]
join [CUSTTRANS]
ON CUSTTRANS.INVOICE=CUSTINVOICEJOUR.INVOICEID
AND [CUSTTRANS].AccountNum = [CUSTINVOICEJOUR].InvoiceAccount
and [CUSTTRANS].TransDate = [CUSTINVOICEJOUR].InvoiceDate
and [CUSTTRANS].Voucher = [CUSTINVOICEJOUR].LedgerVoucher
join CUSTSETTLEMENT
on CUSTSETTLEMENT.TransCompany = [CUSTTRANS].dataAreaId
and CUSTSETTLEMENT.TransRecId = [CUSTTRANS].RecId
and CUSTSETTLEMENT.AccountNum = [CUSTTRANS].AccountNum
WHERE [CUSTSETTLEMENT].DUEDATE = [CUSTINVOICEJOUR].DUEDATE
AND [CUSTSETTLEMENT].DUEDATE between'2017-10-01' AND '2018-09-30'
AND [CUSTSETTLEMENT].TRANSDATE <'2018-10-01 0:0:0'
AND [CUSTINVOICEJOUR].INVOICEID LIKE 'ACP%'
AND [CUSTINVOICEJOUR].INVOICEACCOUNT='100318M'
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