Either use

GROUP BY
CATEGORY, CATEGORIES.NAME;

or drop CATEGORIES.NAME from the SELECT

===========================================

Yves Glodt wrote:

>
> Hello,
>
> I've got a problem with a statement.
> First, this is the DB:
> -----------------------
>
> CREATE TABLE ADDRESS
> (
> CATEGORY INTEGER NOT NULL,
> NAME VARCHAR(100) NOT NULL,
> KEYINDEX INTEGER NOT NULL,
> ADDRESS BLOB SUB_TYPE TEXT SEGMENT SIZE 100,
> DATE_MODIFIED VARCHAR(25),
> HOST_MODIFIED VARCHAR(25),
> USER_MODIFIED VARCHAR(100),
> PRIMARY KEY (KEYINDEX)
> )
> ;
> CREATE INDEX MYX ON ADDRESS (CATEGORY,NAME,KEYINDEX);
>
> CREATE TABLE CATEGORIES
> (
> C_NUMBER INTEGER NOT NULL,
> NAME VARCHAR(50),
> DESCRIPTION VARCHAR(100),
> PRIMARY KEY (C_NUMBER)
> )
> ;
> CREATE INDEX CATX ON CATEGORIES (C_NUMBER);
>
> This is the query:
> ----------------------
> SELECT ADDRESS.CATEGORY,COUNT(ADDRESS.KEYINDEX),CATEGORIES.NAME FROM
> ADDRESS,CATEGORIES WHERE ADDRESS.CATEGORY=CATEGORIES.C_NUMBER GROUP BY
> CATEGORY;
>
> This is the resulting error:
> -------------------------------
> Statement failed, SQLCODE = -104
> Dynamic SQL Error
> -SQL error code = -104
> -invalid column reference
>
> I've tried *exactly* this query with the same db on mysql, and I got a
> result. Therefo I think that the syntax in itself is correct.
> Is there an Interbase (Version is 6.01) limit, or am I the limit :(
>
> I also tried different ways for aliases (A.CATEGORY ... from ADDRESS A),
> but this leads to the same error as well.
>
> (btw, are the indexes ok or should I modify them. There are two from of
> selects: Everythin from a single category, or one specifiy record,
> selected by KEYINDEX)
>
> yves
>
> hm, and p.s.: readline support would SO MUCH enhance isql ;-)
>
> --
> Linux 2.4.9-ac10 #1 Sat Sep 8 18:50:10 CEST 2001 i686
> 4:21pm up 6:13, 1 user, load average: 0.23, 0.19, 0.17
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/