Microsoft Dynamics AX (Archived)

Help translating SQL Query to Dynamics AX2012 AX

(0) Share

Report

Posted on by xdudevrb

195

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

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (0)

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

Like (0)

Report

What "not working" exactly means? I don't see anything obviously wrong with your query, so if you don't explain your problem, I can't help you with it.

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (0)

Report

Hi Martin,

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.

Was this reply helpful? Yes No
krishna.rao@dax 3,006 on at

Like (0)

Report

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..

Was this reply helpful? Yes No
Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report

Your query does return data in my system, therefore your problem is somewhere else.

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (0)

Report

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

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (0)

Report

Hi Martin

Can you please take alook at the image attached to my response.

Was this reply helpful? Yes No
Mark Prouty 1,312 on at

Like (0)

Report

IN AX 2009, I need to omit the table name in the group by statement:

while select sum(Qty) from inventTrans

group by DateFinancial

where inventTrans.ItemId == inventTable.ItemId &&

inventTrans.DateFinancial >= startDate &&

inventTrans.DateFinancial <= endDate &&

(inventTrans.DateFinancial)

Was this reply helpful? Yes No
Suggested answer

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

Like (1)

Report

I already answered that on the DUG forum. These duplicate threads just make things more difficult for me, both as for a contributor and a moderator.

All right, here is my answer again:

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.

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (0)

Report

Hi,

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'

Was this reply helpful? Yes No
Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report

I see what you've develop, but I don't know what you wanted to develop.

First tell us what you're trying to achieve by your query, then we can talk about how to implement it.

Was this reply helpful? Yes No