Hi Guys ,
I have a problem with SQL statements in ax…As it does not support nested queries Is there any alternate way to do this?
A statement to delete all sales order that are open and don’t have a salesline
A statement to bulk delete all sales order that are open and don’t have a salesline (Use delete_recordset)
Select all customers who have more than 10 salesorders (Two Query)
Select all customers who have a salesorder between a given period(Choose the period based on your local system data)
kranthi
January 28, 2013, 10:16am
2
Here is a sample code(not tested)
static void salesTableFetch(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
CustTable custTable;
TransDate fromDate, toDate;
;
// to delete all the orders that dont have a sales Line
delete_from salesTable
where salesTable.SalesStatus == SalesStatus::Backorder
notexists join salesLine
where salesLine.SalesId == salesTable.SalesId;
// get customers that have sales orders > 10
while select count(recId),CustAccount from salesTable
group by custAccount
{
if (salesTable.RecId > 10)
{
info(salesTable.CustAccount);
}
}
// get customers that have sales orders under specified period
while select custTable
exists join salesTable
where salesTable.CustAccount == custTable.AccountNum
&& salesTable.DeliveryDate >= fromDate
&& salesTable.DeliveryDate <= toDate
{
info(custTable.AccountNum);
}
}
Thank You For The solution Kranthi i would like to know how count can be used
I have a query like this?
Select the item that has the largest number of Sales lines (Use:count)
I don’t see any simple query to achieve this. You can use a temporary table concept to achieve this. which holds item Id and record count fields. Fill the temp table by applying a group on itemId from salesTable by finding the count of records. Apply a sort order on the countField in the temporary table to get the item with highest number of salesLines.
I achieved the query in this way…i donno whether it is correct or not…so may i know your opinion on this…
static void CMKQuery04(Args _args)
{
//Select the item that has the largest number of Sales lines (Use:count)
SalesLine salesLine;
int minimum,maximum;
str itemid;
;
minimum = 1;
while select count(recId),itemid from salesLine group by salesLine.ItemId
{
maximum = salesLine.RecId;
if(maximum > minimum)
{
minimum = maximum;
if(salesLIne.RecId == minimum )
{
itemId = salesLIne.ItemId;
}
continue;
}
}
info(strfmt("%1",ItemId));
}
The logic looks good, but it may not work in a scenario like if you have multiple items which has same number of lines(highest lines).
I have last Three More Queries which I could not Address…So please help me
Select all salesline that has a inventory transaction with status(Reserved Physical, Reserve ordered, Ordered)
Select all salesorder who has atleast one salesline that has a inventory transaction with status(Reserved Physical, Reserve ordered, Ordered)(Hint: Multiple level Select statment:3levels)
Select all items in saleslines that has a configuration(inventory dimension) specified in transactions (Multilevel: 3 levels)
Thank You…
kranthi
January 29, 2013, 11:18am
8
here are sample queries
static void queries(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
InventTrans inventTrans;
InventTable inventTable;
InventDim inventDim;
;
while select salesLine
exists join inventTrans
where inventTrans.InventTransId == salesLine.InventTransId
&& (inventTrans.StatusIssue == StatusIssue::ReservPhysical
|| inventTrans.StatusIssue == StatusIssue::ReservOrdered
|| inventTrans.StatusIssue == StatusIssue::OnOrder)
{}
while select salesTable
exists join salesLine
where salesLine.SalesId == salesTable.SalesId
exists join inventTrans
where inventTrans.InventTransId == salesLine.InventTransId
&& (inventTrans.StatusIssue == StatusIssue::ReservPhysical
|| inventTrans.StatusIssue == StatusIssue::ReservOrdered
|| inventTrans.StatusIssue == StatusIssue::OnOrder)
{
info(salesTable.SalesId);
}
while select inventTable
exists join salesLine
where salesLine.ItemId == inventTable.ItemId
exists join inventDim
where inventDim.inventDimId == salesLine.InventDimId
&& inventDim.configId != ‘’
{
info(inventTable.ItemId);
}
}
I have last Three More Queries which I could not Address…So please help me
Select all salesline that has a inventory transaction with status(Reserved Physical, Reserve ordered, Ordered)
Select all salesorder who has atleast one salesline that has a inventory transaction with status(Reserved Physical, Reserve ordered, Ordered)(Hint: Multiple level Select statment:3levels)
Select all items in saleslines that has a configuration(inventory dimension) specified in transactions (Multilevel: 3 levels)
Thank You…
Thank yoU very Much For Your support. I have learnt much with this…
Is ther a command in ax 2009 like delete_recordset??
i jus referred to the post in one of the blogs here
http://mohammadaamirshakil.wordpress.com/2012/07/
i just knew only about delete_from
as i have a query like this i need to address ir
a) Write a statement to bulk delete all sales order that are open and don’t have a salesline (Use delete_recordset)?
kranthi
February 1, 2013, 10:38am
12
I think its a typing mistake.
Have a look at the existing set based operations in AX
http://msdn.microsoft.com/en-us/library/aa673589.aspx
can You help me with this?
when i debug with my query the flow is missing…
Select all items who’s inventory model group is negative inventory flag checked and has a inventory dimension group with the flag inventColourId Yes (Hint: Multiple Joins)
HI kranthi
can i try this logic , to achieve above requirement which is specified by you …
SalesLine salesLine;
int minimum ;
int maximum ;
str itemId,itemid1;
int i;
container con;
;
maximum=1;
while select count(recid),ItemId from salesLine group by ItemId
{
minimum = salesLine.RecId;
if(minimum>maximum)
{
maximum=minimum;
if(salesLIne.RecId == maximum )
{
itemId = salesLIne.ItemId;
if(maximum==maximum)
{
itemid1 = salesLine.ItemId;
con = conins(con,conlen(con)+1,itemid1);
}
continue;
}
}
}
con = conins(con,conlen(con)+1,itemid1);
for(i=1;i<conlen(con);i++)
{
info(conpeek(con,i));
}
info(strfmt("%1",maximum));
//nfo(strfmt("%1,%2",ItemId,itemid1,maximum));
}