This is a followup to something I posted a week or two ago. At that
time I wasn't sure if GROUP BY was allowed in VIEWs, but have since
discovered they are allowed.

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
;


Regards,
Andrew Ferguson
-- Half man, half beast, all Andrew.