select statement clausule not in AX 2012

Finance and Operations/AX Developer Forum
1

hi guys,

Im trying to do this sentence (SQL) in AX, how to do that?

select …

WHERE InventLocationId not in (‘location1’,‘location3’,‘location8’)

Thanks.

2

Hello Rodolfo,

You can do the following:

select field1, field2
from tableA
where tableA.InventLocationId != “location1”
&& tableA.InventLocationId != “location3”
&& tableA.InventLocationId != “location8”;

If that is not an option, then you can use the Query Class and if needed iterate the result of the query. I the following example I am doing the query to the Table InventLocation.

Query query;

QueryRun qr;

QueryBuildRange qbr;

InventLocation inventLocation;

;

query = new Query();

query.addDataSource(tableNum(InventLocation));

qbr = query.dataSourceTable(tableNum(InventLocation)).addRange(fieldNum(InventLocation, InventLocationId));

qbr.value("!location1,!location3,!location8");

//qbr.value(SysQuery::valueNot(“location1”)); SysQuery::valueNot does not accept multiple values

qr = new QueryRun(query);

while(qr.next())

{

inventLocation = qr.get(tableNum(InventLocation));

info(inventLocation.InventLocationId);

}

3

its a good opinion, but the locations are not constants, it’s configurable

4

Then I think you could Read the setup and build a String to set the range