db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kpt. Pickard" <surmada...@centrum.cz>
Subject Problem with group by with Derby
Date Tue, 13 Nov 2007 09:48:02 GMT

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.


Mime
View raw message