Hello,

You can do

select
COUNT(*),
substr(code,1,:Par)
from
STREDISKA
group by
2

it works also for order by clause

but substr in group by clause works with fb 154 in a stored proc

SET TERM ^ ;

CREATE PROCEDURE xxxtest
( par smallint)
RETURNS
( numbercode integer, codepart varchar(50))
AS

BEGIN
if (par is null) then par=3;
if (par<=0) then par=1;
if (par>50) then par=50;
for select COUNT(*), substr(code,1,:Par)
from STREDISKA group by substr(code,1,:Par)
into :numbercode, :codepart do
SUSPEND;
END^

SET TERM ; ^

and
select * from xxxtest(3)


So when do you have this error ?

Preparing the query ? with a query component in Delphi ?

Regards

Alexandre

--- In firebird-support@yahoogroups.com, "lubos2mlx" <lubos2mlx@...>
wrote:

>
> There is a problem since firebird version 1.5.3. If you have
> used :params in GROUP BY clausule, firebird generates 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).
>
> For example, this select works up to 1.5.2 version, but not since
> 1.5.3 :
>
> select
> COUNT(*),
> substr(code,1,:Par)
> from
> STREDISKA
> group by
> substr(code,1,:Par)
>
> if you change :Par by constant number, it works:
>
> select
> COUNT(*),
> substr(code,1,3)
> from
> STREDISKA
> group by
> substr(code,1,3)
>
> You can try it on this table:
> /***********************************/
> /* Table: STREDISKA */
> CREATE TABLE STREDISKA (
> ID INTEGER NOT NULL,
> CODE VARCHAR (50) CHARACTER SET WIN1250 COLLATE WIN1250);
> /* Primary keys definition */
> ALTER TABLE STREDISKA ADD CONSTRAINT PK_STREDISKA PRIMARY KEY (ID);
> /* Indices definition */
> CREATE UNIQUE INDEX PK_STREDISKA ON STREDISKA (ID);
>
> INSERT INTO STREDISKA
> (ID,CODE)
> VALUES
> (1,'001234');
> INSERT INTO STREDISKA
> (ID,CODE)
> VALUES
> (2,'001564');
> INSERT INTO STREDISKA
> (ID,CODE)
> VALUES
> (3,'002666');
> INSERT INTO STREDISKA
> (ID,CODE)
> VALUES
> (4,'003555');
> /**********************************/
>
>
>
>
> Best regards
>
> Bc. ¼uboš Okruhlica
> Programmer, analyst
>