Unexpected Query behaviour

Question

I am trying to execute the following code:

static void ProjTableQuery(Args _args)
{
   Query query;
   QueryBuildDataSource qbds1;
   QueryBuildDataSource qbds2;
   QueryBuildRange qbr1;
   QueryBuildRange qbr2;
   QueryRun queryRun;
   ProjTable projTable;

   query = new Query();

   qbds1 = query.addDataSource(tableNum(ProjTable));
   qbds1.addSortField(
       fieldNum(ProjTable, Name),
       SortOrder::Ascending);

   //qbr1 = qbds1.addRange(fieldNum(ProjTable, Type));
   //qbr1.value(queryValue(ProjType::FixedPrice));

   qbr2 = qbds1.addRange(fieldNum(ProjTable, ProjId));
   qbr2.value(queryValue('0') + '*');

   qbds2 = qbds1.addDataSource(tableNum(ProjEmplTrans));
   qbds2.relations(true);
   qbds2.joinMode(JoinMode::InnerJoin);

   queryRun = new QueryRun(query);

   while (queryRun.next())
   {
       projTable = queryRun.get(tableNum(ProjTable));
       info(strFmt("%1 %2 %3", projTable.ProjId, projTable.Name, projTable.Type));
   }
}

It works fine with those 2 lines commented out. But if I uncomment them, it will not run anymore and will not show any error messages.

As far as I saw, ProjType is an enum and I am sure I have FixedPrice values just checked that in SQL.

This query works for me :) without error. Do you have any `FixedPrice` project starting with `0` **AND** with `ProjEmplTrans`? — Matej, Nov 25 '15 at 15:46
@Matej I can comment out the addRange regarding the ProjId and keep only the one aiming the Type and still no results. — Olaru Mircea, Nov 25 '15 at 15:52
@Matej, I've encountered a strange thing .. when typing the "Type" field, intellisense is not helping at all, just like that "Type" filed would not be present in ProjTable.. — Olaru Mircea, Nov 25 '15 at 15:55
what do you mean by "not running"? it quits after what line? or does it not produce results? the fact that the autocomplete fails might indicate aoi or auc/kti issues or a need for a full compile — Tom V, Nov 25 '15 at 19:28

Jan B. Kjeldsen · Accepted Answer · 2015-11-25T22:09:44.120

This hack is useful for understanding the SQL generated by a query:

query.literals(true);
info(query.datasourceNo(1).toString());

Add the line before the while loop (maybe comment the loop out).
The output will be an almost legal SQL statement (some X++ still shines through though).

The corresponding hack for X++ gives the exact SQL statement:

ProjTable projTable;
select generateonly forceliterals from projTable 
    where ProjTable.Type == ProjType::FixedPrice;
info(projTable.getSQLStatement());

The output is fully legal SQL and can be copy/pasted to the Query Editor.

Unexpected Query behaviour

1 Answers1

Linked