September 14th, 2010, 03:49 PM
I have a SELECT statement with problems. It's large, but it's not complex at all. Here you go:
(select status_n from request_status(r.status)),
(select count(*) from sys$files where id=r.files),
sum(iif(rd.new_status = 25, time_length, 0)) as validate_time,
sum(iif(rd.new_status = 35, time_length, 0)) as analyze_time,
sum(iif(rd.new_status = 65, time_length, 0)) as work_time,
sum(iif(rd.new_status = 75, time_length, 0)) as test_time,
sum(iif(rd.new_status between 90 and 99, time_length, 0)) as reject_count,
sum(iif(rd.new_status = 80, 1, 0))
FROM man$requests r
JOIN man$requestprojects p ON (r.project = p.id)
LEFT JOIN sys$users u_req on (r.requester=u_req.id)
LEFT JOIN sys$users u_ex on (r.requester=u_ex.id)
LEFT JOIN sys$users u_sup on (r.requester=u_sup.id)
LEFT JOIN sys$users u_test on (r.requester=u_test.id)
JOIN man$requestdet rd on (rd.request = r.id)
GROUP BY r.id
I knew before compiling this statement that it will give me this error:
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).
but as you can see i have 2 major tables here MAN$REQUESTS (r) and MAN$REQUESTDET (rd), the rest are just for gathering human readable information (converting IDs into names)
and the simple thing this select has to do is to list different user's worktimes
for every REQUEST i have some REQUESTDET details (there is stored what happened with the request)
Firebird asks me to list all the statements in the GROUP BY clause that contain no aggregate functions
I'd like to avoid that somehow, since i not only have simple fields there but also subselects.
And i don't even understand why Firebird is so strict. In the old Foxpro ages that worked without problems.
September 14th, 2010, 09:24 PM
SQL specification I believe. Required in all SQL databases I know.
See if using the field index numbers will work for grouping if the fields are complex sub queries. I do not think field aliases work for group by.
September 15th, 2010, 05:33 AM
Doesn't work with index numbers. If there's no other solution i'll need to change it into a Stored Procedure, but that will work a little bit slowly
Originally Posted by clivew
or i can eliminate the GROUP BY clause and use subselects at the sum & count part...and that will also be slower ...ok i'll gve a try to see the performance difference at least
September 15th, 2010, 12:25 PM
Try turning the query into a view (without the GROUP BY of course) with field names for the sub-queries, then write your query against the view using the view column names in the GROUP BY clause.
September 17th, 2010, 01:19 AM
You can try to use MAX, something like this :
max(r.project) as project,
max(p.name) as name,
max(r.topic) as topic,
September 18th, 2010, 11:24 AM
aggregate function or the GROUP BY clause
all fields in SELECT clausule, except with functions Max, Sum, Min, AVG, etc... aggregate functions... should be placed in clause SELECT...
so, you can to use the format "CET" to facilitate your work .
tbNameX ( select .... from .... clausulesWhere,group....),
left outer join tbNameY on (....)
remember: ALL tables CET should be in the clausule main SELECT
search in goole for the book Helen Borrie : The Firebird Book: A Reference for Database for Developers