Help translating SQL Query to Dynamics AX2012 AX

Hi everybody,

Sorry if this seems s…d, I’m a newbie to AX2012.

Need your help translating this SQL Server query to AX, can’t get it right on AX. If I add the group by clause it stops working.

Original SQL Server query

select LASTUPDDATEPHYSICAL, AVAILPHYSICAL, INVENTSUM.ITEMID from inventSum
INNER join inventDim
ON inventSum.InventDimId = InventDim.inventDimId
INNER join inventJournaltrans
ON inventJournaltrans.ItemId = inventSum.ItemId
INNER join inventTable
ON inventTable.ItemId = inventSum.ItemId
AND inventTable.dataAreaId = inventSum.dataAreaId
INNER join inventItemgroupitem
ON inventItemgroupitem.ItemId = inventTable.ItemId
AND inventTable.dataAreaId = inventItemgroupitem.ItemDataAreaId
INNER join inventItemgroup
ON inventItemgroupitem.ItemGroupId = inventItemgroup.ItemGroupId
AND inventItemgroupitem.ItemGroupDataAreaId = inventItemgroup.dataAreaId
AND inventSum.AvailPhysical > 0
AND inventItemgroup.gstItemGroupType = ‘RAW’
group by inventsum.ITEMID, LASTUPDDATEPHYSICAL, AVAILPHYSICAL

AX2012 Query (Not working)

while select ItemId,LastUpdDatePhysical,AvailPhysical from inventSum
group by inventsum.ItemId, inventSum.LastUpdDatePhysical, inventSum.AvailPhysical
join inventDim
where inventSum.InventDimId == InventDim.inventDimId
join inventJournaltrans
where inventJournaltrans.ItemId == inventSum.ItemId
join inventTable
where inventTable.ItemId == inventSum.ItemId
&& inventTable.dataAreaId == inventSum.dataAreaId
join inventItemgroupitem
where inventItemgroupitem.ItemId == inventTable.ItemId
&& inventTable.dataAreaId == inventItemgroupitem.ItemDataAreaId
join inventItemgroup
where inventItemgroupitem.ItemGroupId == inventItemgroup.ItemGroupId
&& inventItemgroupitem.ItemGroupDataAreaId == inventItemgroup.dataAreaId
&& inventSum.AvailPhysical > 0
&& inventItemgroup.gstItemGroupType == ‘RAW’

Thanks in advanced for your urgent help

To explain a bit more

When i add the group by clause, no records are return by the query, if I remove the group by, it returns all records just filterd by these

&& inventSum.AvailPhysical > 0

&& inventItemgroup.gstItemGroupType == ‘RAW’

about 4000 records,

When I run the query in SQL SERVER it returns 200 records, those are the records I need for my report.

Do I have to add group by to each table joined??? Can you please show me the correct sintax?

So far I’m stuck.

you have not selected inventDimId from inventsum table so you need to change 1st two columns

while select ItemId,LastUpdDatePhysical,AvailPhysical,InventdimId from inventSum
group by inventsum.ItemId, inventSum.LastUpdDatePhysical, inventSum.AvailPhysical,inventSum.inventDimId

let us know whether you are getting desired result with this changes or not…

Hi Krishna

I’ve created a job to test my DP class, but even with the modifications you suggested, the query still does not return any records, please see attach image.

Do I have to add group by to each table joined??? Can you please show me the correct sintax?

Thanks in advanced for your help

Hi,

You should remove inventSum.AvailPhysical in your group by clause, I don’t understand why you want to group by a qty

Please verify solution if it solves your problem

regards,

Thomas

The screenshot looks as expected. If you think it’s wrong, you have wrong expectations.

If you group a query, you get values only for fields that you group by or that are aggregated (e.g. using sum() function). Because you don’t group by nor aggregate any RecId field, they are all empty. Their values are simply not defined. It’s exactly the same both queries (SQL and X++), because ultimately X++ uses SQL too.

Your current query returns four fields only: ItemId, LastUpdDatePhysical, AvailPhysical and InventDimId. You shouldn’t expect any other value.

Yes Martin,

I understand, this is my new attemp but still no success.

while select ItemId,LastUpdDatePhysical,AvailPhysical,InventDimId from inventSum
group by ItemId, LastUpdDatePhysical, AvailPhysical, InventDimId
join wMSLocationId, inventDimId from inventDim
group by wMSLocationId, inventDimId
where inventSum.InventDimId == InventDim.inventDimId
join gstAgingDate,ItemId from inventJournaltrans
group by gstAgingDate, ItemId
where inventJournaltrans.ItemId == inventSum.ItemId
join BOMUnitId,ItemId,dataAreaId from inventTable
group by BOMUnitId, ItemId, dataAreaId
where inventTable.ItemId == inventSum.ItemId
&& inventTable.dataAreaId == inventSum.dataAreaId
join ItemId,dataAreaId from inventItemgroupitem
group by ItemId, dataAreaId
where inventItemgroupitem.ItemId == inventTable.ItemId
&& inventTable.dataAreaId == inventItemgroupitem.ItemDataAreaId
join ItemGroupId,gstItemGroupType,dataAreaId from inventItemgroup
group by ItemGroupId, gstItemGroupType, dataAreaId
where inventItemgroupitem.ItemGroupId == inventItemgroup.ItemGroupId
&& inventItemgroupitem.ItemGroupDataAreaId == inventItemgroup.dataAreaId
&& inventSum.AvailPhysical > 0
&& inventItemgroup.gstItemGroupType == ‘RAW’

Hello Verci,

I tried your original query, and it works for me if I omit the gstItemGroupType condition. So I think there is an issue at that point.

static void Job2(Args _args)

{

InventSum inventSum;

InventDim inventDim;

InventItemGroup inventItemGroup;

InventItemGroupItem inventItemGroupItem;

InventTable inventTable;

InventJournalTrans inventJournalTrans;

while select ItemId,LastUpdDatePhysical,AvailPhysical from inventSum

group by ItemId, LastUpdDatePhysical, AvailPhysical

join inventDim

where inventSum.InventDimId == InventDim.inventDimId

join inventJournaltrans

where inventJournaltrans.ItemId == inventSum.ItemId

join inventTable

where inventTable.ItemId == inventSum.ItemId

&& inventTable.dataAreaId == inventSum.dataAreaId

join inventItemgroupitem

where inventItemgroupitem.ItemId == inventTable.ItemId

&& inventTable.dataAreaId == inventItemgroupitem.ItemDataAreaId

join inventItemgroup

where inventItemgroupitem.ItemGroupId == inventItemgroup.ItemGroupId

&& inventItemgroupitem.ItemGroupDataAreaId == inventItemgroup.dataAreaId

&& inventSum.AvailPhysical > 0

//&& inventItemgroup.gstItemGroupType == ‘RAW’

{

info(“found it”);

}

You might also try putting the && inventSum.AvailPhysical > 0 up near the selection of inventSum, after the group by, but I don’t really think that should make a difference.

The same question is also being discussed in the duplicate thread on another forum.