william_yuwei schrieb:

> Hi, All
>
> The following query is very simple:
>
> SELECT COUNTRY_CODE, COUNTRY_NAME FROM COUNTRIES
> ORDER BY COUNTRY_NAME
>
> However, I need to add a extra field, DELETABLE, the field which
> indicate if the country record has been used/referenced by records in
> other tables or not, so to use this field to show a state on the
> screen(Grids) visually. I have a lot of SELECT queries which want to do
> the same way. Is there any good way and how to fetch it?
>
> Thanks
>
> William
>

I had a similar problem and this was my solution. You need a view, which
lists all tables and fields that references your table COUNTRIES and a
stored procedure, which looks for every country, how many records in
these tables references this country. If you sum up all results, you get
the number of records which references the country.

The View:

CREATE VIEW V_COUNTRIESRELATIONS(
TABLENAME,
FIELDNAME)
AS
SELECT DISTINCT Index2.RDB$RELATION_NAME, IndexSegment.RDB$FIELD_NAME
FROM RDB$INDICES Index1
JOIN RDB$INDICES Index2
ON Index1.RDB$INDEX_NAME = Index2.RDB$FOREIGN_KEY
JOIN RDB$INDEX_SEGMENTS IndexSegment
ON Index2.RDB$INDEX_NAME = IndexSegment.RDB$INDEX_NAME
WHERE Index1.RDB$RELATION_NAME = 'COUNTRIES';

The stored procedure:

CREATE PROCEDURE SP_GET_COUNTRIES_REL_COUNT (
COUNTRYID BIGINT)
RETURNS (
TABLENAME VARCHAR(31),
FIELDNAME VARCHAR(31),
REFCOUNT INTEGER)
AS
begin
FOR SELECT TableName, FieldName
FROM V_COUNTRIESRELATIONS
INTO :TableName, :FieldName
DO
BEGIN
EXECUTE statement
'SELECT Count(' || FieldName || ')' ||
' FROM ' || TableName ||
' WHERE ' || FieldName || ' = ' || CountryID
INTO :RefCount;
SUSPEND;
END
end

The SQL:

SELECT CountryID, SUM(RefCount)
FROM Countries, SP_GET_COUNTRIES_REL_COUNT(CountryID)
GROUP By CountryID

If the result is 0, you can delete it, otherwise it has references.

CountryID is the primary Key field of your Countries-Table. If this is
Country_Code you have to change CountryID Bigint to whatever type is
Country_Code. This solution works only, if all references are made by
foreign keys and not by trigger programming or something else.
I don't know if this is a good solution, or if it has any serious
disadvantages, but for me it is working fine. So any comments from
others are welcome. :-)