You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Kpt. Pickard" <su...@centrum.cz> on 2007/11/13 10:48:02 UTC
Problem with group by with Derby
Hi everybody,
can you somebody tell me how to rewrite this query (problem with coalesce
and group by clause):
select distinct count (d.C_DOCUMENTID) as entries,c.C_CATEGORYTEXTKEY,
c.C_LEVEL, c.C_RANK,
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || '.*.*') as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
cast((c.C_CATEGORYTEXTKEY || '.*.*') as char(50)) and
T_BOILERPLATE.C_LANGUAGE = ? ),
c.C_CATEGORYTEXTKEY)) )) )) )) as desription
from T_DOCUMENT d, T_CATEGORY c
where d.C_FKCATEGORYID = c.C_CATEGORYID and
cast (d.C_FKSTATUSID as char(20)) like cast (? as char(20)) and
cast (d.C_FKDOCUMENTTYPEID as char(20)) like cast (? as char(20)) and
( cast (d.C_FKCREATIONWFID as char(20)) like cast (? as char(20)) or
cast (d.C_FKRESUBMISSIONWFID as char(20)) like cast (? as char(20))) and
cast (d.C_CATEGORYBRANCH as char(20)) like cast (? as char(20)) and
cast (d.C_CREATORDATE as date ) >= cast ( ? as date ) and
cast (d.C_CREATORDATE as date ) < cast ( ? as date )
group by c.C_CATEGORYTEXTKEY, c.C_LEVEL, c.C_RANK
order by c.C_LEVEL, desription
Thanks
--
View this message in context: http://www.nabble.com/Problem-with-group-by-with-Derby-tf4796749.html#a13722671
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problem with group by with Derby
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
I guess you get the error message
ERROR 42X04: Column 'something' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'something' is not a column in the target table.
Which is a correct error message. The 'COALESCE..... AS desription' part
of your query is not an aggregate and neither part of a HAVING clause
nor a GROUP BY clause. Therefore the query is illegal according to the
SQL standard.
>>>>>>>>>>>> Kpt. Pickard wrote (2007-11-13 01:48:02):
>
> Hi everybody,
>
> can you somebody tell me how to rewrite this query (problem with coalesce
> and group by clause):
>
> select distinct count (d.C_DOCUMENTID) as entries,c.C_CATEGORYTEXTKEY,
> c.C_LEVEL, c.C_RANK,
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || '.*.*') as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || ?) as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> COALESCE ( ( select distinct T_BOILERPLATE.C_DESCRIPTION from
> T_BOILERPLATE where cast (T_BOILERPLATE.C_KEY as char(50)) like
> cast((c.C_CATEGORYTEXTKEY || '.*.*') as char(50)) and
> T_BOILERPLATE.C_LANGUAGE = ? ),
> c.C_CATEGORYTEXTKEY)) )) )) )) as desription
> from T_DOCUMENT d, T_CATEGORY c
> where d.C_FKCATEGORYID = c.C_CATEGORYID and
> cast (d.C_FKSTATUSID as char(20)) like cast (? as char(20)) and
> cast (d.C_FKDOCUMENTTYPEID as char(20)) like cast (? as char(20)) and
> ( cast (d.C_FKCREATIONWFID as char(20)) like cast (? as char(20)) or
> cast (d.C_FKRESUBMISSIONWFID as char(20)) like cast (? as char(20))) and
> cast (d.C_CATEGORYBRANCH as char(20)) like cast (? as char(20)) and
> cast (d.C_CREATORDATE as date ) >= cast ( ? as date ) and
> cast (d.C_CREATORDATE as date ) < cast ( ? as date )
> group by c.C_CATEGORYTEXTKEY, c.C_LEVEL, c.C_RANK
> order by c.C_LEVEL, desription
>
>
> Thanks
> --
> View this message in context: http://www.nabble.com/Problem-with-group-by-with-Derby-tf4796749.html#a13722671
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway