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

Tue, 27 Apr 2004 05:52:34 -0700

Hi Werner
 
I think what's wrong is that you've missed ItemId from the list of columns to select. In your standard SQL92 syntax you had the Qty, Amount and ItemId columns but you didn't include the ItemId in the Axapta translation. If you want to group by ItemId then ItemId needs to be included in the selected column list or you will get a syntax error on the compile.
 
Hope that solves it for you.
 
Regards
 
Malcolm Burtt
Global IT Group.
-----Original Message-----
From: Werner Briedl [mailto:[EMAIL PROTECTED]
Sent: 22 April 2004 10:02
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

Reply via email to