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
;