cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Sonney <ke...@webslingerz.com>
Subject Re: <sql:count-attribute>
Date Thu, 18 May 2000 13:19:11 GMT
On Thu, 18 May 2000, Sebastien Koechlin I-VISION wrote:
> I have problems with sqltaglib and <sql:count-attribute>,
> My query looks like :
> 	SELECT DISTINCT firstname, lastname FROM status;
> 
> to count rows, I think this query is translated into:
> 	SELECT COUNT(distinct firstname) FROM emp;

Which is valid, except the table name got changed. I'm guessing you meant
"FROM status". There isn't any code in the SQLTaglib to change the FROM
clause of a query.

> which is wrong, 
> 	Oracle return the wrong result 
> 	and Postgresql an error.

According to SQL92, "SELECT COUNT(DISTINCT column) FROM table;" is a legal
query. Does this work properly in the sql CLI for the databases, or using
JDBC outside of Cocoon (i.e. in a class that *JUST* runs thew
query)? AFAIK, this is perfectly legal, and if it is returning an error,
ot incorrect results, then either the Database or the JDBC driver is
possibly to blame. I can test on Oracle, but I don't happen to ahve a
postgreSQL server set up at the moment. 

> This query does not work either with postgresql:
> 	SELECT firstname FROM emp ORDER BY firstname;
> because
> 	SELECT COUNT(firstname) FROM emp ORDER BY firstname;
> does : "Illegal use of aggregates or non-group column in target list",
> order by shouldn't be used.

Hmmm. Donald? This is possibly a real bug. OTOH, in order to prevent this,
you'll have to find the end of the "where" clause by checking for any
modifiers (ORDER, GOUP, etc) and cutting the substring off there. 

Anyone try this type of query elsewhere (oracle, mysql, db/2) and see a
similar result, or is this just postgreSQL? 

> This last one does not work too:
> 	SELECT emp.*;
> 
> but it's not a well writen query.

Correct. a proper query is :
SELECT emp.* FROM emp;

I try not to use "SELECT table.column " clauses unless I'm querying
multiple tables, myself. I don't even think the last clause is even valid
SQL, but I could be wrong. 

-- 
- Kevin Sonney
  kevin@webslingerZ.com


Mime
View raw message