How to set a where condition between two fields of a same table in AOT query

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Thanks Nikolaos, I was able to solve my issue through the website you posted.

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.

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

Great to hear that! Please mark the helpful answer(s) as verified so that others can benefit from them. Thanks!

Was this reply helpful? Yes No
YokTH 5 on at

Like (1)

Report

//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)));

Was this reply helpful? Yes No