RE: [development-axapta] Query with ORDER BY and GROUP BY
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:02To:
[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.
RE: [development-axapta] Query with ORDER BY and GROUP BY
My
guess is that order by sum... is the culprit
try
using the alias instead
HTH
Pieter
-Original Message-From: Don Price
[mailto:[EMAIL PROTECTED]Sent: 22. april 2004
17:50To: [EMAIL PROTECTED]Subject: RE:
[development-axapta] Query with ORDER BY and GROUP BY
One additional thing don’t forget
to set the company when going external, here is select statement I did on a
memo field
sqlSTR = strFmt("select recID from AAA_Tracker where notes like '\%%1\%' AND dataAreaId = '%2'", Part2, datAreaId);
you can’t
do that in Axapta either.
Don
-Original
Message-From: Werner
Briedl [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 22,
2004 4:02
AMTo: [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.
RE: [development-axapta] Query with ORDER BY and GROUP BY
One additional thing don’t
forget to set the company when going external, here is select statement I did on a memo
field
sqlSTR = strFmt("select
recID from AAA_Tracker
where notes like '\%%1\%' AND dataAreaId =
'%2'", Part2, datAreaId);
you can’t
do that in Axapta either.
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.
RE: [development-axapta] Query with ORDER BY and GROUP BY
You can always go external right to
sql using
Connection con = new Connection();
Statement sqlStmt
= Con.createStatement();
ResultSet sqlTable;
Str
sqlStr;
sqlStr
=
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.
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 -
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.
