Yep, if I comment out the GROUP BY line in the VIEW then it returns
the correct count of 8488. With the SELECT on its own, inclusion of
the GROUP BY makes no difference: always 8488. Am I missing
something about VIEWS?

Regards,
Andrew Ferguson
-- The difference is, we know it's an illusion.

--- In ib-support@y..., "csswa" <csswa@y...> wrote:
> A view and a select with identical DML return different record
counts:
>
> This SELECT reports a count of 8488 records...
>
> SELECT T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY
> FROM
> T_POSTCODE T1, T_REGION T2, T_CODESET T3, T_CODESET T4
> WHERE
> T1.PCODE_REGION_FK = T2.REG_PK
> AND T2.REG_REGION_FK = T3.CODE_PK
> AND T3.CODE_SET = 'REGION'
> AND T3.CODE_CODE STARTING WITH 'AU'
> AND T4.CODE_SET = 'COUNTRY'
> AND T4.CODE_CODE = T2.REG_COUNTRY_FK
> GROUP BY T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY
> ;
>
> This VIEW reports a count of 8257 records...
>
> CREATE VIEW V_TEMP_POSTCODES(
> COUNTRY,
> REGION,
> SUBURB)
> AS
> SELECT T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY
> FROM
> T_POSTCODE T1, T_REGION T2, T_CODESET T3, T_CODESET T4
> WHERE
> T1.PCODE_REGION_FK = T2.REG_PK
> AND T2.REG_REGION_FK = T3.CODE_PK
> AND T3.CODE_SET = 'REGION'
> AND T3.CODE_CODE STARTING WITH 'AU'
> AND T4.CODE_SET = 'COUNTRY'
> AND T4.CODE_CODE = T2.REG_COUNTRY_FK
> GROUP BY T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY
> ;
>
> As you can see, they differ only in the View header definition.
Any
> known bugs associated with this? Possibly the 'group by' is acting
> differently within the view?
>
> Regards,
> Andrew Ferguson
> -- Not to be confused with the raving lunatic of the same name.