db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Problem with group by with Derby
Date Tue, 13 Nov 2007 10:26:25 GMT
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

Mime
View raw message