Talk:Expressions in query ranges
From Axaptapedia
Please if the previous editor of the article reads this, add a note somewhere on this page. The examples shown in the new version of the article are not related to the special Expressions syntax!
I've pasted the previous edit below, pending resolution of this issue
The syntax that is shown in this edit unfortunately does not work for the Query Expressions syntax, or at least I don't think so!
If you can find a way to do something similar to:
queryBuildRange.value(strFmt('((Dimension[1] == %1) || (Dimension[2] == "%2"))',
"some dim1 value",
"some dim2 value"));
then please add it here, and modify the article accordingly AJ
Previous edit below - please comment above if you think it should be restored or have further information
Contents |
[edit] Limitations
There are two major limitations to the Query Expressions syntax. The first is the loss of support for wildcards and comma-separated range values, and the second is the inability to reference array fields such as dimensions. (SEE CORRECTION HERE UNDER!!!)
Whilst in standard queries you can specify "AA*" or "A,B,C" as criteria, and they will be parsed by Axapta and sent through correctly to the database, these will simply be passed directly through when using the Query Expressions engine. As a result, they will not return the expected results. On a related noted, the use of 'like' is not supported, so there is no way to use wildcards in any form.
Unfortunately, if you wish to filter on array fields in an Axapta table, such as the Dimensions field, there is no way to do so using the Query Expressions syntax. (SEE CORRECTION HERE UNDER!!!)
Correction :
To filter on array fields in an Axapta table you should use the "fieldId2Ext(fieldId,tableIndex)" method. Axapta will not show you IntelliSense help for this function but will still compile it.
Example of use :
queryBuildRange = dsLedgerTrans.addRange(fieldId2Ext(fieldNum(LedgerTrans, Dimension),1));
Addition to Correction :
If you want to range your datasource according to specific values this would be the way to do it (LedgerTrans_org would be the records holding the dimension values to compare against). The sql result here would be an dimension[1]="dim1" && dimension[2]="dim2". Not sure about an OR syntax.
dsLedgerTrans.addRange(fieldId2Ext(fieldNum(LedgerTrans,Dimension),1)).value( strFmt('%1',LedgerTrans_org.Dimension[1])); dsLedgerTrans.addRange(fieldId2Ext(fieldNum(LedgerTrans,Dimension),2)).value( strFmt('%1',LedgerTrans_org.Dimension[2])); dsLedgerTrans.addRange(fieldId2Ext(fieldNum(LedgerTrans,Dimension),3)).value( strFmt('%1',LedgerTrans_org.Dimension[3]));
[edit] Dimensions
queryBuildRange.value(strFmt('((Dimension[1] == %1) || (Dimension[2] == "%2"))',
"some dim1 value",
"some dim2 value"));
You can do the following in Axapta with as
queryBuildRange.value(strFmt('((Dimension == %1) || (Dimension2_ == "%2"))',
"some dim1 value",
"some dim2 value"));
Also You can use SysDictTable class
queryBuildRange.value(strFmt('((%1 == %3) || (%2 == "%4"))', SysDictTable::fieldnameSQL(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), 1), FieldNameGenerationMode::FieldList), SysDictTable::fieldnameSQL(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), 2), FieldNameGenerationMode::FieldList), "some dim1 value", "some dim2 value"));
Have you tried what is entered above? I tried it and I get the dreaded error:
Query extended range failure: Right parenthesis expected near pos 0.
62.58.93.177 01:39, 29 Dec 2005 (PST)
[edit] queryValue
add queryValue call to all parameters :
static void Test_QR(Args _args) { Query query = new Query(); QueryBuildDataSource ds= query.addDataSource(tableNum(InventTable)); QueryBuildRange range= ds.addRange(fieldNum(InventTable, DataAreaId)); QueryRun qr; ; query.literals(1); // Add our range range.value(strFmt('(ItemId == "%1")', queryValue('test "number one"'))); //try to uncomment this and you'll receive error //range.value(strFmt('(ItemId == "%1")', 'test "number one"')); qr=new QueryRun(query); while(qr.next()) { print qr.get(tableNum(InventTable)).RecId; } pause; }
[edit] Limitation: Multiple-table expression query ranges within Forms
While the ability to include multiple tables works fine for a new Query() object, the principle does not work when working with Data Source objects used in Forms. For example, if a Form contain CustTable and SalesTable, applying a filter to CustTable via SalesTable.DataAreaId does not work. Using SQL Profiler, it becomes apparent that the sql query generated by Axapta translates the table name and field names to an empty string. I.e. "CustTable.Blocked = 1" translates to: "' '.' ' = 1"
I've also tried this is the Init() but with no success.
public void executeQuery() { QueryBuildRange qbr; str criteria; ; qbr = this.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable, DataAreaId)); criteria = strFmt( '(%1.%2==%3) && ((%1.%4==%5) || (%6.%7==%8) || (%6.%9==%10))', tableStr(SalesTable), // %1 - ok! fieldStr(SalesTable, DeliveryDate), // %2 - ok! Date2StrXpp(deliveryDate), // %3 - ok! fieldStr(SalesTable, Syn_OrderOnhold), // %4 - ok! any2int(NoYes::Yes), // %5 - ok! tableStr(CustTable), // %6 - Fails! fieldStr(CustTable, Blocked), // %7 - Fails! any2int(CustVendorBlocked::All), // %8 - Fails! fieldStr(CustTable, Blocked), // %9 - Fails! any2int(CustVendorBlocked::Invoice) // %10 - Fails! ); qbr.value(criteria); super(); }
[edit] AX 5.0 supports the wildcards in Query Range expressions.
AX 5.0 allows you to put into the Query object expressions like
(AccountNum LIKE "*AA*" || Name LIKE "*AA*")
and does create proper SQL for the query.
