As a footnote to this from last week, the solution was to place an
extra field in the GROUP BY to ensure uniqueness. I changed the
GROUP BY to:

GROUP BY T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY,
T1.PCODE_POSTCODE, T1.PCODE_PK;

with T1.PCODE_PK being the unique integer pk of that table. Lo and
behold I get a record count of 8489 instead of the bogus 8488 counted
previously. It appears that Australia Post have a duplicated suburb
record in their public postcodes file.

Regards,
Andrew Ferguson
-- A rhetorical answer to a non-rhetorical question.

--- In ib-support@y..., "csswa" <csswa@y...> wrote:
> The DSQL code and VIEW code are identical, yet a COUNT of records
> returned by the VIEW is 8488 and the COUNT of records in the DSQL
> results is 8489. Why is there one less record in the VIEW when the
> code is identical, as you can see below?
>
> DSQL code:
>
> SELECT
> T4.CODE_DESC AS COUNTRY,
> T3.CODE_DISPLAY AS REGION,
> T1.PCODE_LOCALITY AS SUBURB,
> T1.PCODE_POSTCODE AS POSTCODE
> 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 T4.CODE_SET = 'COUNTRY'
> AND T4.CODE_CODE = T2.REG_COUNTRY_FK
> GROUP BY T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY,
> T1.PCODE_POSTCODE
>
> VIEW definition:
>
> CREATE VIEW V_TEMP_POSTCODES(
> COUNTRY,
> REGION,
> SUBURB,
> POSTCODE)
> AS
> SELECT T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY,
> T1.PCODE_POSTCODE
> 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 T4.CODE_SET = 'COUNTRY'
> AND T4.CODE_CODE = T2.REG_COUNTRY_FK
> GROUP BY T4.CODE_DESC, T3.CODE_DISPLAY, T1.PCODE_LOCALITY,
> T1.PCODE_POSTCODE
> ;