You’re offline. This is a read only version of the page.
Hi,
I have an SQL as below,
select CUS.INVOICE,CUS.AMOUNTCUR from dbo.CUSTTRANS CUS where CUS.AMOUNTCUR > 0 AND CUS.AMOUNTCUR <> CUS.SETTLEAMOUNTCUR
I'm trying to convert this to AOT query and I'm having trouble in setting a where condition between two fields of the same table.
The TSQL for the AOT query is as below,
SELECT AmountCur, Invoice FROM CustTrans(CustTrans_1) WHERE ((AmountCur>0)) AND ((NOT (SettleAmountCur = 0)))
The results between SQL and DAX AOT Query don't match.
The problem here is with CUS.AMOUNTCUR <> CUS.SETTLEAMOUNTCUR.
How can I set this in the AOT query.
Please help.
Thanks,
Praveen.
*This post is locked for comments
//this.query().datasourceTable(TableNum(CustTrans)).addRange(FieldNum(CustTrans,recid)).value(strFmt('(%1 - %2 > 0)', fieldStr(custtrans, amountcur),fieldStr(custtrans, settleamountcur) ) );
this.query().datasourceTable(TableNum(CustTrans)).addRange(FieldNum(CustTrans,recid)).value(strFmt('((%1 != %2))',fieldStr(CustTrans, AmountCur),fieldStr(CustTrans, SettleAmountCur)));
Thanks Nikolaos, I was able to solve my issue through the website you posted.
Share
Report
All responses (
Answers (
QueryBuildDataSource qbr1;
QueryRun qrrun;
Query qr = new Query(queryStr(TestQuery));
qbr1 = qr.dataSourceTable(tablenum(CustTrans));
qbr1.addSortField(fieldNum(CustTrans,Invoice),SortOrder::Descending);
qbr1.name("CustTrans");
qbr1.addRange(fieldNum(CustTrans, Invoice)).value(
strFmt('((%1.%2 != %1.%3))',
qbr1.name(),
fieldStr(CustTrans, AmountCur),
fieldStr(CustTrans, SettleAmountCur)));
info(qr.toString());
The above code changes to the AOT query gives me the following TSQL which is exactly what I was looking for.
SELECT AccountNum, AmountCur, Invoice FROM CustTrans(CustTrans) ORDER BY CustTrans.Invoice DESC WHERE ((AmountCur>0)) AND ((((CustTrans.AmountCur != CustTrans.SettleAmountCur))))
Thank you.
Praveen.
Please see this article: docs.microsoft.com/.../using-expressions-in-query-ranges
You might need to set the range in code instead of the AOT query definition.
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