Hi

> Feature request:
>
> SQL:
>
> SELECT
> COUNT(sub_select.*) as COUNT, sub_select.user_name,
> sub_select.password
> FROM
> (
> SELECT
> user_name, password
> FROM
> USER sub_select
> WHERE
>
> user_name LIKE '%%'
> AND password LIKE '%%'
> AND name LIKE '%%'
> AND surname LIKE '%%'
> )
>
>
> Content of my "USER" table:
>
> user_name password name surname
> _________ ______ __________ ________
>
> g aa aa
> h bb bb
> i cc cc
> j dd dd
> k ee ee
> l ff ff
>
>
> Result of SQL must be :
>
> COUNT user_name password
> _____ ______ __________
> 6 g aa
> 6 h bb
> 6 i cc
> 6 j dd
> 6 k ee
> 6 l ff

The SQL you're proposing here has a so called derived table and this is
already implemented in the FB 2.0 source, but it doesn't gona work with
COUNT() as you would like to see. Simple because it isn't possible and
effective at all.

Why don't you use a uncorrelated sub-query.

SELECT
(SELECT COUNT(*) FROM USER) AS COUNT,
user_name,
password
FROM
USER sub_select
WHERE
user_name LIKE '%%' AND
password LIKE '%%' AND
name LIKE '%%' AND
surname LIKE '%%'

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81