--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 10:20 PM 14/12/2010, raivis83 wrote:
> >Hi All!
> >
> >I have table and view, thats returning grouped field with the count of fields records in it.
> >After upgrading from 2.1 to 2.5 I noticed that select count is returning null. Has anyone experienced anything like this and can confirm that it' s a bug and has reported it?
> >
> >Here is DDL:
> >--------------------------------------------------------------------------------------
> >-- Test table T_EMPLOYEE:
> >SET SQL DIALECT 3;
> >
> >CREATE TABLE T_EMPLOYEE (
> > ID INTEGER,
> > NAME VARCHAR(20),
> > DEPT_NO INTEGER
> >);
> >
> >-- View
> >SET SQL DIALECT 3;
> >CREATE VIEW V_EMP_DEP(
> > DEPT_NO,
> > EMP_COUNT)
> >AS
> >select
> > e.dept_no,
> > (select count(*) from t_employee e1 where e1.dept_no = e.dept_no)
>
> Try removing this:
>
> /* as emp_count */ - it's not valid syntax for CREATE VIEW so I guess it's possible nobody has tested this, amongst all the changes that have been done for views DDL.
>
> > from t_employee e
> > group by e.dept_no
> >;
>
> ./heLen
>

You are not quite right. Here is FB2.1 language reference for creating view:

CREATE VIEW viewname [<full_column_list>]
AS
SELECT <column_def> [, <column_def> ...]
FROM ...
[WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])

<column_def> ::= {source_col | expr} [[AS] colalias]

As you can see, column_def can have aliases. There's no language reference for 2.5 so i can't tell if it has changed. But I doubt it has. Anyway I just tried removing alias and it didn't help.