Hi Varden, the query you posted doesn't work
Query q = new Query();
QueryBuildDataSource dsSalesTable;
QueryBuildDataSource dsSalesLine;
QueryRun qr;
SalesTable SalesTable;
dsSalesTable = q.addDataSource(tablenum(SalesTable));
dsSalesLine = dsSalesTable.addDataSource(tablenum(SalesLine));
dsSalesLine.addLink(FieldNum(SalesTable, SalesId), FieldNum
(SalesLine,SalesId));
dsSalesLine.JoinMode(JoinMode::InnerJoin);
dsSalesLine.addSortField(FieldNum(SalesLine, SalesId));
dsSalesLine.orderMode(ordermode::GroupBy);
dsSalesLine.addSelectionField(FieldNum(SalesLine, LineAmount),
SelectionField::Sum);
qr = new QueryRun(q);
while (qr.next())
{
SalesTable = qr.get(tablenum(SalesTable));
print SalesTable.DeliveryAddress;
}
pause;
You will see that SalesTable.DeliveryAddress is `', no fields are filled
I was going to asking you a different way to make the query to work
when I found the problem
You missed an istruction like this
dsSalesLine.addSelectionField(FieldNum(SalesTable,DeliveryAddress,SelectionField::Min);
Notice that you have to specify a type different than
SelectionField::Database
Bye
--- In Axapta-Knowledge-Village@yahoogroups.com, Varden Morris
<[EMAIL PROTECTED]> wrote:
>
> Hi Anton,
>
> I tried this in a job andI didn't get any error:
>
> Query q = new Query();
> QueryBuildDataSource dsSalesTable;
> QueryBuildDataSource dsSalesLine;
> QueryRun qr;
> SalesTable SalesTable;
> dsSalesTable = q.addDataSource(tablenum(SalesTable));
> dsSalesLine = dsSalesTable.addDataSource(tablenum(SalesLine));
> dsSalesLine.addLink(FieldNum(SalesTable, SalesId), FieldNum
> (SalesLine,SalesId));
> dsSalesLine.JoinMode(JoinMode::InnerJoin);
> dsSalesLine.addSortField(FieldNum(SalesLine, SalesId));
> dsSalesLine.orderMode(ordermode::GroupBy);
> dsSalesLine.addSelectionField(FieldNum(SalesLine, LineAmount),
> SelectionField::Sum);
> qr = new QueryRun(q);
> while (qr.next())
> {
> SalesTable = qr.get(tablenum(SalesTable));
> print SalesTable.DeliveryAddress;
> }
> pause;
>
> anton_tjiptadi <[EMAIL PROTECTED]> wrote:
>
> Hi,
> How to create X++ query for Group By ??
>
> My select statement is simply like this (in Transact SQL)
>
> select a.salesid, sum(b.lineamount) from salestable a, salesline b
> where a.salesid=b.salesid
> group by a.salesid
>
> I try to build in X++ like this, but it cause error said that there's
> no Group by
>
> dsSalesTable = q.addDataSource(tablenum(SalesTable));
> dsSalesLine = dsSalesTable.addDataSource(tablenum(SalesLine));
> dsSalesLine.addLink(FieldNum(SalesTable, SalesId), FieldNum
> (SalesLine,SalesId));
> dsSalesLine.JoinMode(JoinMode::InnerJoin);
> dsSalesLine.addSortField(FieldNum(SalesLine, SalesId));
> dsSalesLine.orderMode(ordermode::GroupBy);
> dsSalesLine.addSelectionField(FieldNum(SalesLine, LineAmount),
> SelectionField::Sum);
>
> Thanks in advance,
>
>
>
>
>
>
> Sharing the knowledge on Axapta.
>
>
>
> ---------------------------------
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/Axapta-Knowledge-Village/
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Take Yahoo! Mail with you! Get it on your mobile phone.
>
