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

Thu, 22 Apr 2004 06:40:23 -0700

Hi!
 
Thank you for your answer, but the problem isn't the criteria with the InvoiceDate, that works. The Problem is, that Axapta doesn't accept the GROUP BY and the ORDER BY clause in one Statement.
 
while select sum(Qty), sum(LineAmount) from custInvoiceTrans group by ItemId order by sum(LineAmount) desc
{ 
}
 
regards,
Werner.

Von: Ing. Gonzalo Bastos S. [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 22. April 2004 15:41
An: [EMAIL PROTECTED]
Betreff: Re: [development-axapta] Query with ORDER BY and GROUP BY

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 -----
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

Reply via email to