[FIXED] Firebird aggregate query problem

Posts   
 
    
johnh
User
Posts: 21
Joined: 28-Sep-2003
# Posted on: 22-Dec-2004 08:56:10   

When I run the following query it works fine in firebird, but when I try to add it using LLBLGEN I get the following message:

"RefreshCatalog::View 'V_CATCH_BY_WEIGHT' removed from list due to cast exception in Field population."


CREATE VIEW V_CATCH_BY_WEIGHT (
    BT_BOAT_NAME,
    SP_NAME,
    CD_WEIGHT)
AS
SELECT DISTINCT
  BOAT.BT_BOAT_NAME,
  SPECIES.SP_NAME,
  SUM(CATCH_DETAIL.CD_WEIGHT)

FROM
  CATCH_DETAIL
  INNER JOIN CATCH ON (CATCH_DETAIL.CTC_ID = CATCH.CTC_ID)
  INNER JOIN BOAT ON (CATCH_DETAIL.BT_ID = BOAT.BT_ID)
  INNER JOIN SPECIES ON (CATCH_DETAIL.SP_ID = SPECIES.SP_ID)
GROUP BY  BOAT.BT_BOAT_NAME,SPECIES.SP_NAME
;

The problem is evident when grouping by fields which are VARCHAR (BT_BOAT_NAME or SP_NAME).

It works fine if I only group by fields of type int (e.g. BT_ID or SP_ID - both integer) This query works fine and and can be added to a LLBLGEN project


CREATE VIEW V_CATCH_BY_WEIGHT (
    BT_BOAT_NAME,
    SP_NAME,
    CD_WEIGHT)
AS
SELECT DISTINCT
  BOAT.BT_ID,
  SPECIES.SP_ID,
  SUM(CATCH_DETAIL.CD_WEIGHT)

FROM
  CATCH_DETAIL
  INNER JOIN CATCH ON (CATCH_DETAIL.CTC_ID = CATCH.CTC_ID)
  INNER JOIN BOAT ON (CATCH_DETAIL.BT_ID = BOAT.BT_ID)
  INNER JOIN SPECIES ON (CATCH_DETAIL.SP_ID = SPECIES.SP_ID)
GROUP BY  BOAT.BT_ID,SPECIES.SP_ID
;

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Dec-2004 10:00:52   

I'll check it out.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Dec-2004 17:47:58   

Fixed. Please check the hotfix in the drivers section.

Frans Bouma | Lead developer LLBLGen Pro