sql statement in x++

(0) Share

Report

Posted on by Jenny -

120

Hi, Could anyone please tells me how to write this code in X++?
select salestable.InventSiteId, SALESTABLE.SALESRESPONSIBLE, SALESLINE.CUSTGROUP,
inventtable.INVENTTYPE, inventtable.INVENTSUBTYPE, SUM(salesline.SALESQTY)
from salestable, SALESLINE, INVENTTABLE
where salestable.SALESID == salesline.SALESID &&
inventtable.ITEMGROUPID == salesline.CUSTGROUP &&
INVENTTABLE.ITEMID == salesline.ITEMID &&
SALESTABLE.SALESSTATUS == SalesStatus::Invoiced
group by salestable.INVENTSITEID, salestable.SALESRESPONSIBLE,
salesline.CUSTGROUP, inventtable.INVENTTYPE,
inventtable.INVENTSUBTYPE
order by salestable.INVENTSITEID, salesline.CUSTGROUP, salestable.SALESRESPONSIBLE,
inventtable.INVENTTYPE, INVENTTABLE.INVENTSUBTYPE;

The statement works fine in SQL Query, but not in AX.

Thanks for the help.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Here are some references on how to create a query using x++:

msdn.microsoft.com/.../aa656402.aspx

msdn.microsoft.com/.../aa638454.aspx

Was this reply helpful? Yes No
Malcolm Burtt 1,475 on at

Like (0)

Report
Copy link

Link copied!

Hi

You say that you cannot get the statement to work in AX. In what sense does it not work?

My guess is that you're having trouble structuring the X++ syntax correctly to handle the group by across mutliple tables where fields (e.g. InventSiteId) appear in multiple tables. If that's your problem you could try adding multiple group by clauses, one for each table. e.g.

select InventSiteId, SalesUnitId from salesTable

group by InventSiteId, SalesUnitId

where salesTable.SALESSTATUS == SalesStatus::Invoiced

join ItemType, ItemBuyerGroupId from inventTable

group by ItemType, ItemBuyerGroupId

where inventTable.ItemId == salesLine.ItemId

join custGroup, sum(SalesQty) from salesLine

group by custGroup

where salesTable.salesId == SalesLine.SalesId

Note that I'm using different fields than you as this code is coming from AX 2012 and the fields you listed don't exist on the same tables.

This might still leave you with a problem because I see you have specified the order by field list differently from the group by field list and I don't think AX is going to let you do that.

Regards

Malcolm Burtt

K3 Retail

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (0)

Report
Copy link

Link copied!

I've a similar problem, converting a sql server query to ax2012 x++, this is new attemp but still no success, can you help me with the correct syntax.

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
KyleLeBarre 747 on at

Like (0)

Report
Copy link

Link copied!

How did you even find this 3 year old post?

Was this reply helpful? Yes No
xdudevrb 195 on at

Like (1)

Report
Copy link

Link copied!

Desperation and patience! :-D

Was this reply helpful? Yes No