Andris Spruds wrote:
> Hello,
> I'm tryin to set up a small SAPDB-driven web site, however I'm having
> problems with GROUP BY syntax. I would like to fetch some
> articles, their
> authors and number of comments attached to each article. I
> used MySQL syntax
> where simply using c.id (query is bellow) as a group column would do.
> However, that would not work with SAPDB as SAPDB insists on
> specifying ALL
> selected columns as group columns, what's more, in this case
> it does not
> work because of 'Too many order columns'. I would be grateful
> if someone
> could point me to the right way of doing this!
If GROUP BY is used, every value_expression not used within
a set-function (SUM, MIN, COUNT, ...) has to be specified in the group-by-clause.
The error means that you have to many (no, YOU do NOT have to many) or
you have order columns which together exceed a length of a little bit smaller than 1KB.
--> make the select-list a little bit smaller.
>
> SELECT ar.id, count(c.id) as cmnts, ar.author author_id, ar.category
> category_id, ar.updated, ar.title, ar.synopsis,
> u.name || ' ' || u.surname author_name, u.email
> author_email,u.username
> author_username
> FROM ar_articles ar, users u, ar_comments c
> WHERE usr.id=ar.author AND c.article_id = ar.id
> GROUP BY c.id
And what you (I assume) want to know is the number of comments for
each article together with a bunch of other things (author,...).
Try this :
SELECT ar.id, c.cmnts, ar.author author_id, ar.category
category_id, ar.updated, ar.title, ar.synopsis,
u.name || ' ' || u.surname author_name, u.email
author_email,u.username author_username
FROM ar_articles ar, users u,
(select count(id) as cmnts, article_id from ar_comments group by article_id ) c
WHERE usr.id=ar.author AND c.article_id = ar.id
Good luck and a nice weekend
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
