Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to make X++ query ...
Finance | Project Operations, Human Resources, ...
Unanswered

How to make X++ query with where clause and OR conditions

(0) ShareShare
ReportReport
Posted on by paoDEV 5

Hi, I try to reproduce a very simple SQL query in X with :
- 3 tables
- where clause
- OR conditions
- LIKE

I would like the following result in SQL :

SELECT * FROM TABLE1 tab1
LEFT JOIN TABLE2 tab2 on tab2.x = tab1.x
LEFT JOIN TABLE3 tab3 on tab3.x = tab1.x
WHERE (tab1.a LIKE '񥧠OR tab1.aa LIKE '񥧠OR tab2.b LIKE '񥧠or tab3.c LIKE '񥧩

I tried addRange and addQueryFilter but the result is not good.

QueryBuildDataSource tab1;
QueryBuildDataSource tab2;
QueryBuildDataSource tab3;    	

tab1 = query.dataSourceTable(tableNum(TABLE1)); 
tab2 = query.dataSourceTable(tableNum(TABLE2));
tab3 = query.dataSourceTable(tableNum(TABLE3));                    

// 1 - ADDQUERYFILTER
query.addQueryFilter(tab1, fieldStr(TABLE1, a)).value('F1');
query.addQueryFilter(tab1, fieldStr(TABLE1,aa)).value('F1');
query.addQueryFilter(tab2, fieldStr(TABLE2,b)).value('F1');
query.addQueryFilter(tab3, fieldStr(TABLE3, c)).value('F1') 

// 2 - ADDRANGE
QueryBuildRange qbr; 
qbr = tab1.addRange(fieldNum(TABLE1, a));
qbr = tab1.addRange(fieldNum(TABLE1, aa));
qbr = tab2.addRange(fieldNum(TABLE2, b));
qbr = tab3.addRange(fieldNum(TABLE3, c));
qbr.value(strFmt('((%2 LIKE \'%%1%\') || (%3 LIKE \'%%1%\') || (%4 LIKE \'%%1%\') || (%5 LIKE \'%%1\'))', 'F1', fieldStr(TABLE1, a), fieldStr(TABLE1, aa) fieldStr(TABLE2, b), fieldStr(TABLE3, c) ));

I'm lost, how to do it in X ?

Thanks.

  • paoDEV Profile Picture
    paoDEV 5 on at
    RE: How to make X++ query with where clause and OR conditions

    I'm not write SQL queries in my code, the SQL I've put it's the translate of the X query object.

    I'm only tried to write an simple example with random tables and fields to expose what I'm expected.  I just want to know if it's possible to make a X query with several filters based on differents tables and with "OR" conditions. The "addQueryFilter" seems to be the more appropriate way but the "OR" conditions are missing for the example I have find.

    The following example in X 

    FieldId itemIdField = fieldNum(InventTable, ItemId);
FieldId itemTypeField = fieldNum(InventTable, ItemType);
Query query1 = new Query();
QueryBuildDataSource ds1 = query1.addDataSource(tableNum(InventTable));
ds1.addRange(itemIdField).value('Item01');
ds1.addRange(itemIdField).value('Item02');
ds1.addRange(itemIdField).value('(ItemId > "Item04")');
ds1.addRange(itemTypeField).value(queryValue(ItemType::Item));
ds1.addRange(itemTypeField).value(queryValue(ItemType::Service));
query1.addQueryFilter(ds1, itemIdField).value('Item02');
query1.addQueryFilter(ds1, itemIdField).value('Item03');
query1.addQueryFilter(ds1, itemIdField).value('(ItemId < "Item01")');
query1.addQueryFilter(ds1, itemTypeField).value(queryValue(ItemType::Service));

    give this SQL result : 

    SELECT *
FROM INVENTTABLE T1
WHERE PARTITION=68719480489
 AND DATAAREAID='dat'
 AND (ITEMID='Item01' OR ITEMID='Item02' OR ITEMID>'Item04')
 AND (ITEMTYPE=0 OR ITEMTYPE=2)
 AND (ITEMID='Item02' OR ITEMID='Item03' OR ITEMID<'Item01')
 AND (ITEMTYPE=2)
ORDER BY T1.ITEMTYPE, T1.ITEMID

    what I'm expected for my X query is approximatively the same SQL translate query but with the adding of other tables/field (with OUTER JOIN) , my starting point is the SQL(see it below) and I want the way to do it in X . The LEFT JOIN are already maked in my Query Object, I only want to add a "WHERE" clause like this example : 

      SELECT * FROM TABLE1 tab1
    LEFT JOIN TABLE2 tab2 on tab2.x = tab1.x
    LEFT JOIN TABLE3 tab3 on tab3.x = tab1.x
    WHERE (tab1.a LIKE '񥧠OR tab1.aa LIKE '񥧠OR tab2.b LIKE '񥧠OR tab3.c LIKE '񥧩

  • Martin Dráb Profile Picture
    Martin Dráb 231,947 Most Valuable Professional on at
    RE: How to make X++ query with where clause and OR conditions

    What is the business case for such a query? It doesn't look meaningful to me.

    Also, there is a bug. If you write direct SQL queries, you must deal with partitions by yourself. You forgot to include Partition in join conditions, therefore you may be joining records from completely different partitions (such as those created for automated tests). Fortunately this will be done automatically for you when you run X++ code.

  • paoDEV Profile Picture
    paoDEV 5 on at
    RE: How to make X++ query with where clause and OR conditions

    Here is another example with AX tables of what I expect as a result : 

    select   dpt.PARTITION, dpt.recid, * from dirpartytable  dpt
        INNER JOIN DIRPARTYLOCATION dpl on dpl.RECID = dpt.RECID 
        INNER JOIN LOGISTICSLOCATION ll on ll.RECID = dpl.LOCATION
        where (dpt.HOBBIES LIKE '1򥧠OR dpl.POSTALADDRESSROLES LIKE 
        '1򥧠 OR ll.DESCRIPTION LIKE '1򥧠)

    Maybe that would be more appropriate ? 

  • Martin Dráb Profile Picture
    Martin Dráb 231,947 Most Valuable Professional on at
    RE: How to make X++ query with where clause and OR conditions

    Are Table1 - Table3 your tables?

    If you have problem with querying data, it usually means your model isn't designed correctly.

    For example, the need to search for "EUR" inside text fields suggest that the first normal form was likely violated. Even if you're able to write such a query, you would end up with very inefficient execution. I strongly recommend reviewing your data model. Ask if you need help with the design. Unfortunately it's impossible to give you any concrete advice on it with no information about your tables.

  • paoDEV Profile Picture
    paoDEV 5 on at
    RE: How to make X++ query with where clause and OR conditions

    Thanks for your response, I'm sorry I'm beginner in X Dynamics 365 and I'm not very good in english too.

    I tried to write a "simple" example of what my code globaly do for this moment (for privacy it's not an extract of my code) and what I want. I'm agree that there are mistakes and lack of explaination.

    So I have find an internet example that are more concrete :

    "That is exactly the difference between QueryBuildRange and QueryFilter when used on an outer join. The QueryBuildRange will go in the ON clause, whereas QueryFilter will go in the WHERE clause"

    ADDRANGE give :

    SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        AND SALESTABLE.CURRENCYCODE = 'EUR'

    ADDQUERYFILTER give : 

    SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        WHERE SALESTABLE.CURRENCYCODE = 'EUR'

    In my case I want the "where clause" but with multiple fields from other tables "like this" : 

    SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        OUTER JOIN TABLE1 TAB1 ON TAB1.ID = CUSTTABLE.ACCOUNTNUM
        OUTER JOIN TABLE2 TAB2 ON TAB2.ID = SALESTABLE.CUSTACCOUNT
        OUTER JOIN TABLE3 TAB3 ON TAB3.ID = CUSTTABLE.ACCOUNTNUM
        WHERE (TABLE1.x LIKE '%EUR%' OR TABLE1.y LIKE '%EUR%' 
        OR TABLE2.x LIKE '%EUR%' OR TABLE3.x LIKE '%EUR%' )

    I tried but I don't know how to do to add multiple filter.
    Thanks

  • Martin Dráb Profile Picture
    Martin Dráb 231,947 Most Valuable Professional on at
    RE: How to make X++ query with where clause and OR conditions

    Which version of AX are you talking about? If the code above compiles, it must mean that you're talking about D365FO. If so, let me move this thread to the right forum. Also, the category clearly should be Development / Customization / SDK instead of Other. Let me fix it for you.

    One issues is that you didn't add conditions for joins.

    "1 - ADDQUERYFILTER" can't give you the requested result.

    "2 - ADDRANGE" looks like the right approach, but it seems that you have some issues there. Unfortunately "the result is not good" isn't a very detailed description, but I think we can make at least some progress even without proper information.

    First of all, let's simplify this:

    qbr = tab1.addRange(fieldNum(TABLE1, a));
qbr = tab1.addRange(fieldNum(TABLE1, aa));
qbr = tab2.addRange(fieldNum(TABLE2, b));
qbr = tab3.addRange(fieldNum(TABLE3, c));

    You keep overriding the same variable, so it's (almost) the same thing as mere:

    qbr = tab3.addRange(fieldNum(TABLE3, c));

    Also note that it doesn't matter which field you use.

    Now let's look at this:

    qbr.value(strFmt('((%2 LIKE \'%%1%\') || (%3 LIKE \'%%1%\') || (%4 LIKE \'%%1%\') || (%5 LIKE \'%%1\'))',
	'F1',
	fieldStr(TABLE1, a),
	fieldStr(TABLE1, aa)
	fieldStr(TABLE2, b),
	fieldStr(TABLE3, c) ));

    I don't think it'll even compile, because you're missing a comma at line 4.

    Another problem is that you didn't specify data sources of those fields. The documentation explicitly says that you should ("Use the dataSource.field notation for fields from other data sources in the query").

    And you seem to be missing the trailing % for the value of Table3.c.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,274 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,947 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans