Thanks. That was an oversight by me. When using group by the field has to be
apart of an aggregation before it can be selected.
Varden Morris
85 Saddleland Close N.E.
Calgary, Alberta T3J 5J5
Canada
(403) 366-8434 (H)
(403) 615-1604 (C)
[EMAIL PROTECTED]
----- Original Message ----
From: gomezdegomera <[EMAIL PROTECTED]>
To: Axapta-Knowledge-Village@yahoogroups.com
Sent: Monday, July 30, 2007 2:51:10 PM
Subject: [Axapta-Knowledge-Village] Re: Group by in X++ ??
Hi Varden, the query you posted doesn't work
Query q = new Query();
QueryBuildDataSourc e dsSalesTable;
QueryBuildDataSourc e 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. addSelectionFiel d(FieldNum( SalesLine, LineAmount),
SelectionField: :Sum);
qr = new QueryRun(q);
while (qr.next())
{
SalesTable = qr.get(tablenum( SalesTable) );
print SalesTable.Delivery Address;
}
pause;
You will see that SalesTable.Delivery Address 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. addSelectionFiel d(FieldNum( SalesTable, DeliveryAddress,
SelectionField: :Min);
Notice that you have to specify a type different than
SelectionField: :Database
Bye
--- In Axapta-Knowledge- [EMAIL PROTECTED] ups.com, Varden Morris
<[EMAIL PROTECTED] > wrote:
>
> Hi Anton,
>
> I tried this in a job andI didn't get any error:
>
> Query q = new Query();
> QueryBuildDataSourc e dsSalesTable;
> QueryBuildDataSourc e 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. addSelectionFiel d(FieldNum( SalesLine, LineAmount),
> SelectionField: :Sum);
> qr = new QueryRun(q);
> while (qr.next())
> {
> SalesTable = qr.get(tablenum( SalesTable) );
> print SalesTable.Delivery Address;
> }
> pause;
>
> anton_tjiptadi <anton_tjiptadi@ ...> 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. addSelectionFiel d(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:
> Axapta-Knowledge- Village-unsubscr [EMAIL PROTECTED] com
>
> 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.
>
____________________________________________________________________________________
Shape Yahoo! in your own image. Join our Network Research Panel today!
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7
[Non-text portions of this message have been removed]
Sharing the knowledge on Axapta.
Visit www.frappr.com/axapta for axapta friends.
Come and choose your preferred name for Microsoft Dynamics AX on the Axapta
Knowledge Village, Visit www.axapta-knowledge-village.tk
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
