Group by query with inner join + exists join

[Justin Biggs]

Hi guys,

I'm trying to get this query to work right, and I'm having a very difficult
time. I'm querying InventTrans joined to InventDim, summing Qty,
CostAmountPhysical, CostAmountPosted, and CostAmountAdjustment, and grouping
by InventTrans.ItemId as well as selected fields from InventDim (which fields
to group by are selected by the user at run-time using InventDimParm).

This works wonderfully, except when the user wants to add an exists join to
InventTable to add a range on something like ItemType, PrimaryVendorId, etc.
When this is done, the InventTrans datasource seems to lose the join to
InventDim, and only picks up the Exists Join to InventTable.

Looping through all the datasources on my Query object finds me all three
tables, and the criteria I put on InventDim is still there, but it doesn't
appear in the InventTrans_ds.toString() output. Here's a sample output from
the three data sources toString() method:

InventTrans:

SELECT SUM(Qty), SUM(CostAmountPosted), SUM(CostAmountPhysical),
SUM(CostAmountAdjustment) FROM InventTrans GROUP BY InventTrans.ItemId,
InventDim.InventLocationId WHERE ((ItemId = N'4508A')) EXISTS JOIN * FROM
InventTable WHERE InventTrans.ItemId = InventTable.ItemId AND
((PrimaryVendorId = N'502189'))

InventDim:

SELECT * FROM InventDim WHERE InventTrans.inventDimId =
InventDim.inventDimId AND ((InventLocationId = N'850'))

InventTable:

SELECT * FROM InventTable WHERE InventTrans.ItemId = InventTable.ItemId AND
((PrimaryVendorId = N'502189'))

Notice how the child datasources InventDim and InventTable both have
criteria specified on them, but only the criteria from InventTable is shown
in the datasource for InventTrans. I'm baffled, and I hope someone can point
out what I'm doing wrong. Thanks.

--
Best Regards,
Justin

[Vanya Kashperuk Ivan at dot nospam]

There isn't really anything that you are doing wrong.
It's just the specific behavior of AX. When doing an exist join that results
into the query becoming a tree instead of a line, it drops some of the
datasources.

things you should do
- try setting fetchMode on all datasources to OneToOne instead of OneToMany
- reorder the datasources in the query, so that it becomes a line:
-- inventDim -> inventTrans -> inventTable
instead of
inventTrans
- inventDim
- inventTable

--
Kashperuk Ivan (Vanya), SDET, Inventory management, Microsoft Dynamics AX
My blog - http://kashperuk.blogspot.com
Download MorphX IT in Russian - http://www.lulu.com/content/723888

[Justin Biggs]

Hi Vanya,

As always, spot on with advice. Re-arranging the query to be InventTable ->
InventTrans -> InventDim worked like a charm. Thanks very much.

--
Best Regards,
Justin