-------------------------------------------------------------------------------- Hi, I have the following problem: I woould like to realize a Join from SQL in C/AL. So I need an Outer Join and also some usual Joins, but I don’t know If there exists an easy way to realize that in C/AL. Could anyone help me, maybe there are some equivalent operations in C/AL . Thank you very much for help…
The SetRange & SetFilter C/AL commands are the ones that are used to apply selection criteria on a recordset. If your required recordset is very complex and it is not possible to create it using SetRanges and SetFilters on a single record variable then you can use a temporary table (a normal Rec type variable with its Temporary property set to Yes). Use multiple sets of SetRanges/SetFilters on a normal rec type variable to achieve your required filtering and after each set add the records to the temporary table. You can also do things similar to joins e.g. to get a list of all sales orders for customers with a balance over x you could iterate through the Sales Header Table, go ang get the corresponding customer, if the customer balance is over x then add then create an entry in your Sales Header Temp Table with a copy of the current Sales Header. At the end of your iteration the temp table contains only the Sales Headers that meet your criteria. and you can then run a form or report on it or use it to modify the real underlying table. I hope this helps. Chris. When you are finnished the temporary table contains your complex recordset.
SQL JOINS as you are looking for them do not exist in C/AL. You would need to create another table that would have details from each of the tables you wish to join, making sure you set up all the necessary keys, etc. Also, you would have to make some function in C/AL that would update the intermediary table with the data. The only other method I can think of is doing some coding that performs this. Basically, a manual join. If you are looking at JOIN from within the SQL environment, you can access the Navision data this way, but of course it will not be available from within Navision.
Uhlig, Khristopher is correct. One thing to add if you are using MS-SQL, you will not be able to easily reproduce sum fields. See example I put out on the forum some time ago called “Reproduce Nav Sum Field in SQL”. Hope it helps, Bill