Query x++ with conditional OR

Finance and Operations/AX Developer Forum
1

Hi all,

I’m using AX2004.
Help me please.
Me need build query by using QueryBuildDataSource
SQL Query - Select * from T_WriteOnRepair where inwork=1 or createdDate=getDate()
Result 3 rows (2 rows for inwork=1 and 1 row for createdDate=getDate() )

i wrote job

QueryBuildDataSource qbds;
Query q = new Query();
QueryRun QueryRun;
T_WriteOnRepair T_WriteOnRepair;

;
qbds = q.addDataSource(tablenum(T_WriteOnRepair));
qbds.addRange(fieldNum(T_WriteOnRepair, inwork)).value(
strFmt(’( (%1 == %2) or (%3 == %4) )’,
fieldStr(T_WriteOnRepair, inwork), //%1
“1”, //%2
fieldStr(T_WriteOnRepair, CREATEDDATE), //%3
QueryValue(systemdateget())
));

toString
SELECT * FROM T_WriteOnRepair WHERE ((( (inwork == 1) or (CREATEDDATE == “12.10.2016”) )))
in SQL Menegment Studio- query run successfully - Selected 3 rows
But, in Axapta
Result - Error extended range query: expected right parenthesis next to the 20.
Why ???

Ok, write strFmt(’( (%1 == %2) ) or (%3 == %4) )’,

toString

SELECT * FROM T_WriteOnRepair WHERE ((( (inwork == 1)) or (CREATEDDATE == “12.10.2016”) )))

in SQL Menegment Studio - Incorrect syntax near the structure “).”
in Axapta query run successfully BUT Selected 2 rows (only where inwork =1)

Why do not selected row for 'or (CREATEDDATE == “12.10.2016”) ’ - ???

2

You’ve chosen the right approach (unfortunately this kind of things in ugly in AX), but you didn’t format the date correctly. Use date2StrXpp() instead of queryValue().

By the way, what’s your version of AX? There is nothing like AX 2004. Also, please attach some tags next time.

3

Thanks,
I wrong , really need to use instead date2StrXpp() instead of queryValue() and in the string strFmt(’( (%1 == %2) ) or (%3 == %4) )’, to use || instead ‘or’

Correct
queryBuildDataSource.addRange(fieldNum(T_WriteOnRepair, inWork)).value(
strFmt(’((%1 == %2) || (%3 == %4))’,
fieldStr(T_WriteOnRepair, inwork), //%1
1, //%2
fieldStr(T_WriteOnRepair, CREATEDDATE), //%3
date2StrXpp(systemdateget()) ));

I use Microsoft Dynamics AX4.0