SQL statement "IN"

Hello, Does anyone know how you can use the IN statement of SQL in Axapta? I have something like this in SQL select sum(Qty) from reqTrans where (RefType = 1 and itemid = ‘01.01.0006’ and (CovInventDimId IN (select inventdimid from InventDim where InventDim.InventLocationId = ‘MAG’))) in Axapta, I don’t seem to get there in one statement I’ve tried this, but it is not good select sum(Qty) from l_reqTrans where (l_reqTrans.RefType == ReqRefType::InventOnHand && l_reqTrans.itemid == g_inventTable.ItemId && (l_reqTrans.CovInventDimId IN (select InventDim where InventDim.InventLocationId == g_inventtable.inventlocationid()).InventDimId); Anyone an idea? thx, Kathleen

Hi Kathleen Guess you can try using ‘Connection class’ for this requirement. You can find this under AOT → System Documentation → Classes. Hope this helps Harish Mohanbabu

Hi, If you use that, are you not creating a new connection to the database? Is there no way to get the current connection? something like : Connection conn; conn = x::GetConnection(); Or am I being confused? thx, Kathleen

Hello, You’re almost there. Try this. select sum(Qty) from l_reqTrans where (l_reqTrans.RefType == ReqRefType::InventOnHand && l_reqTrans.itemid == g_inventTable.ItemId && l_reqTrans.CovInventDimId == (select InventDim where InventDim.InventLocationId == g_inventTable.inventLocationId()).inventDimId); /Peter Karlsson

Peter, I’ve tried that one, but by using “… == (select …” , only the first result of “select…” is compared with the CovInventDim, whereas there are multiple results to the “select …”. Kathleen

Hi Kathleen I just want to clarify few things here - X++ provides numerous ways to have ‘user defined’ connections by means of classes like - Connection, UserConnection, ODBCConnection etc. The main difference between Connection class and other classes are - Connection class provides a hook into the currently open connection where as others not. In other words, Connection class runs in the same transaction space as the application connection. Application connection is the one that is used for running the business logic of the application. UserConnection class - though it uses same properties as the application connection, it runs in its own transaction space. Primarily Axapta uses this connection to generate number sequence and other system management tasks. ODBCConnection lets programmer to access any ODBC data source by providing details like data source name, user name and password. Hope this clarifies your query. Harish Mohanbabu

Hello, You are right, and thats what IN do in SQL [:I] I don’t know where you are goning to use this code, but is it possible to build the query “the other way around”? like this: while select inventDim where InventDim.InventLocationId == ‘MAG’ { qty = qty + (select sum(Qty) from ReqTrans where ReqTrans.CovInventDimId == inventDim.inventDimId).Qty; } Good luck! /Peter