Talk:Expressions in query ranges

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

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.

<xpp> 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])); 

</xpp>

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

<xpp>

 queryBuildRange.value(strFmt('((%1 == %3) || (%2 == "%4"))', 

SysDictTable::fieldnameSQL(tablenum(), fieldid2ext(fieldnum(, Dimension), 1), FieldNameGenerationMode::FieldList), SysDictTable::fieldnameSQL(tablenum(), fieldid2ext(fieldnum(, Dimension), 2), FieldNameGenerationMode::FieldList), "some dim1 value", "some dim2 value")); </xpp>

---

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)

I believe the issue is because the fieldnames need to be predicated by the table name. I have found this solution elsewhere in Axaptapedia:

<xpp> queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 == "%5"))',

   queryBuildDataSource.name(),

fieldid2name(tablenum(), fieldid2ext(fieldnum(, <field>), Dimensions::code2ArrayIdx(SysDimension::Department))), fieldid2name(tablenum(), fieldid2ext(fieldnum(, <field>), Dimensions::code2ArrayIdx(SysDimension::Center))), "some dim1 value", "some dim2 value")); </xpp> When I use this structure, I am able to make my query work without the error. Please pay attention to the quotes around the string values %4 and %5. The quotes around the string values are necessary as well.

queryValue

add queryValue call to all parameters :

<xpp> 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;

} </xpp>

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.

<xpp> 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();

} </xpp>

AX 5.0 supports the wildcards in Query Range expressions.

AX 5.0 allows you to put into the Query object expressions like <xpp> (AccountNum LIKE "*AA*" || Name LIKE "*AA*") </xpp>

and does create proper SQL for the query.

Query Expression syntax for array fields

1) It does work now at least in AX 2009 RU4 kernel. 2) It works only with the datasource name specified otherwise you'll get an error. 3) If you have an earlier AX 2009 kernel version installed or if you have AX 4 installed - please test if it also works with your kernel version and correct the article if it does.