> Hi Adam
>
> The output i require is for the following query
> I have six tables .. and i have to retrieve about 300 fields from all
> the six tables. The primary key
> for all the tables is the "CO_CODE" and the "YEAR" field. In each of
> the table there are multiple records for
> each "CO_CODE" and "YEAR". I want to fetch the distinct fields only.
>
> My query is somewhat like this
>
> SELECT DISTINCT TADMIN.NAME AS "A",TAG.STO AS "B",TLATFIN.YEND
> AS "C",TBS1.CPY AS "D",TBS2.OTH AS "E",TNETBLOCK.ESTCAP AS "F",....
>
> FROM
> (SELECT * FROM TADMINISTRATOR) AS TADMIN,
> (SELECT * FROM AG) AS TAG,
> (SELECT * FROM LATFIN) AS TLATFIN,
> (SELECT * FROM BS1) AS TBS1,
> (SELECT * FROM NETBLOCK) AS TNETBLOCK,
> (SELECT * FROM BS2) AS TBS2
>
> WHERE TADMIN.CO_CODE = TAG.CO_CODE AND
> TAG.CO_CODE = TLATFIN.CO_CODE AND
> TADMIN.CO_CODE = TBS1.CO_CODE AND
> TADMIN.CO_CODE = TNETBLOCK.CO_CODE AND
> TNETBLOCK.CO_CODE = TLATFIN.CO_CODE AND
> TBS1.CO_CODE = TLATFIN.CO_CODE AND
> TADMIN.CO_CODE = TBS2.CO_CODE AND
> TBS2.CO_CODE=TLATFIN.CO_CODE AND
> TAG."YEAR" = TLATFIN.LATEST AND
> TBS1."YEAR" = TLATFIN.LATEST AND
> TNETBLOCK."YEAR" = TLATFIN.LATEST AND
> TBS2."YEAR"=TLATFIN.LATEST AND
> TAG."YEAR" = TBS1."YEAR" AND
> TAG."YEAR" = TNETBLOCK."YEAR" AND
> TAG."YEAR" = TBS2."YEAR"
>
> Thanx
> Sathiesh
>

You can't even run such a query in Firebird 1.5 because derived tables
are not supported until Firebird 2 is released (it is still release
candidate which means past beta but not yet production safe).

However it seems to me to be a slow way of doing it. Perhaps you are
unaware of the 'join' syntax.

select *
from TADMINISTRATOR TADMIN
JOIN AG TAG on (TADMIN.CO_CODE = TAG.CO_CODE AND TAG."YEAR" =
TLATFIN.LATEST)
JOIN LATFIN TLATFIN ON (TADMIN.CO_CODE = TLATFIN.CO_CODE)
JOIN BS1 TBS1 ON (TADMIN.CO_CODE = TBS1.CO_CODE AND TBS1."YEAR" =
TLATFIN.LATEST AND TAG."YEAR" = TBS1."YEAR")
JOIN NETBLOCK TNETBLOCK ON (TADMIN.CO_CODE = TNETBLOCK.CO_CODE AND
TNETBLOCK."YEAR" = TLATFIN.LATEST AND TAG."YEAR" = TNETBLOCK."YEAR")
JOIN BS2 TBS2 ON (TADMIN.CO_CODE = TBS2.CO_CODE AND
TBS2."YEAR"=TLATFIN.LATEST AND TAG."YEAR" = TBS2."YEAR")

The lines will wrap in this message, but normally they won't and it is
much easier to read. I have noticed that you unnecessarily check
equality of several conditions that are implicitly equal based on
other conditions. I did get a bit lost, but you will have an
understanding of these tables and what keys are used to link them.

I imagine that a join will be significantly faster than emulating a
join using derived tables.

But to your problem, a distinct must check whether any of the 300+
fields are different (by definition). The only way I can see this
possibly working is if there a subset (of under 256 of these fields)
that if distinct would guarantee the rest of them unique? If so, you
could create a view or stored procedure to collect the distinct key
fields, then join to the table to collect all the data.

Adam