RE: [development-axapta] Query with ORDER BY and GROUP BY

[Don Price]

You can always go external right to sql using     Connection    con = new Connection();    Statement     sqlStmt = Con.createStatement();    ResultSet     sqlTable;   Str                  sqlStr;    sqlStr = <your select statement>   sqlTable = sqlStmt.executeQuery(sqlSTR);     while (sqlTable.next()){          set1.add(sqlTable.getInt(1));   //or whatever you need     }   Don -----Original Message----- From: Werner Briedl [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 22, 2004 4:02 AM To: [EMAIL PROTECTED] Subject: [development-axapta] Query with ORDER BY and GROUP BY   Hi there!   Can i use the ORDER BY and the GROUP BY Clause in one SQL Statement?   In SQL92 it would look like this:      SELECT     SUM(QTY) AS Qty, SUM(LINEAMOUNT) AS Amount, ITEMID    FROM         CUSTINVOICETRANS    WHERE     (INVOICEDATE = CONVERT(DATETIME, '2004-01-07 00:00:00', 102))    GROUP BY ITEMID    ORDER BY SUM(LINEAMOUNT) DESC   So i tried it in Axapta:       while select sum(Qty), sum(LineAmount) from custInvoiceTrans group by ItemId order by sum(LineAmount) desc                                            where custInvoiceTrans.InvoiceDate == str2date('20040107', 321)     {         print custInvoiceTrans.itemId, ', ', custInvoiceTrans.Qty, ', ', custInvoiceTrans.LineAmount;     }       But the compiler raises a Syntax Error :-(   Has anybody an idea?   Regards, Werner.   Yahoo! Groups Links To visit your group on the web, go to: http://groups.yahoo.com/group/development-axapta/   To unsubscribe from this group, send an email to: [EMAIL PROTECTED]   Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.