How to do WHEREIN or contain in x++?

(0) Share

Report

Posted on by Community Member

Why x++ is not supporting WHEREIN or contain in sql? Is there any workaround of doing in x++? I have been searching for one hour but haven't found anything.

Please remove Microsoft Dynamics AX filter as not it is called D365 For Operation it is so confusing :(

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (1)

Martin Dráb 237,948 Most Valuable Professional on at

Like (0)

Report

You already commented thread the answered thread IN clause in Select Query X++, where you can see several workarounds, so I'm not sure why you ask whether any exist.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am trying to move business logic from c# to x++ for my D365FO app. I thought it's going to be a lot easier and less restriction on join but it didn't have simple staff like "IN" or "Contains" so I was surprised.

Can you show me how to achieve "IN clause" using exists join in x++? I did some research but only found the solution for AX2012.

www.tech.alirazazaidi.com/in-statement-functionality-dynamics-ax

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Select and query syntax has not been changed since 2012, so any solution for 2012 will work for D365 as well.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks. How about temporary table? Is there any resource I can look to see how I can achieve this? I tried to join temporary table with real table in c# but it wasn't successful.(it does compile and no error but can't get joined result back) so I am not sure this can be done in X++

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

In x++ you have 2 types of temp tables: inMemory and TempDB. TempDB tables are actual tempDB tables in SQL, so you can prepopulate it and use in exist join. Please refer to documentation for details and examples of TempDB table usage msdn.microsoft.com/.../gg845661.aspx

Was this reply helpful? Yes No
Suggested answer

Mahmoud Hakim 17,887 on at

Like (0)

Report

in caluse not supported in x++

And you can use Temp table like sql , just set table type to tempdp

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok so in order to do "in clause" in x++ we need to make query twice or more depending on how many conditions you want to specify? Saying I have three different tables products, categories, and product_category_items and trying to display name of products and categories which belong to both 'electronics' and 'kitchen' categories.

1. Looping through on product table and find any products match with both 'electronics' and 'kitchen' and add matched rows in to temporaryTable temp_product_category(making sure category name is saved)

2. join product and temp_product_category to get the final results?

I this the way I need to do in order to get "IN Clause" in x++?

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

I'm not sure why you cannot use "or", something like this (just a pseudo select) :

Select productname from products

Inner join categoryname from categories

where product.category == category.categiryid && (category.categoryname == "kitchen" || category.categoryname == "electronics')

Was this reply helpful? Yes No
Suggested answer

Sukrut Parab 71,710 Moderator on at

Like (0)

Report

A simple join between two tables product and category should do work for you with proper where condition with your categories you want . I am not sure why you are looking for IN statement

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am trying to make it so that it works on any selected categories (could be 2 or 3 categories) with some more conditions and I will be joining 4-5 tables

Was this reply helpful? Yes No