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

[Ing. Gonzalo Bastos S.]

Hi Werner:   The sintax of the first SQL Statement is good, a SQL Server Query, but in Axapta you can't use at where sintax like custInvoiceTrans.InvoiceDate == str2date('20040107', 321), you only can use Table fields like Custtable.Accountnum = Custtrans.Accountnum by example. You can't use functions in where statement. Instead you should use something like:   while select sum(Qty), sum(LineAmount), InvoiceDate from custInvoiceTrans group by ItemId order by sum(LineAmount) desc      {        If (custInvoiceTrans.InvoiceDate == str2date('20040107',                        321)                                          {             print custInvoiceTrans.itemId, ', ', custInvoiceTrans.Qty, ', ',             custInvoiceTrans.LineAmount;         }            } ----- Original Message ----- From: Werner Briedl To: [EMAIL PROTECTED] Sent: Thursday, April 22, 2004 3:01 AM 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:           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.