UNION in NOT IN

[donvittorio]

Hi,

I have an Interbase 6 database with around 200 tables, some of which have an associated audit table named AUDIT_tablename (e.g. AGENT and AUDIT_AGENT). I am trying to find all tables in the database which do not already have an audit table, and I'm finding it a bit tricky. As far as I know the only place to find tables is in the RDB$RELATIONS system table, but this also holds views, so my select statement needs to be along the lines of:

select rdb$relation_name
from rdb$relations
where rdb$system_flag = 0
and rdb$relation_name not in
(select distinct rdb$view_name from rdb$view_relations
UNION
select r1.rdb$relation_name
from rdb$relations r1, rdb$relations r2
where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)

but this gives me an error (code 104) on the 'UNION'. Does anyone know why?
I can get around it by doing this:

select rdb$relation_name
from rdb$relations r1
where rdb$system_flag = 0
and rdb$relation_name not in
(select distinct rdb$view_name from rdb$view_relations)
and rdb$relation_name not in
(select r1.rdb$relation_name
from rdb$relations r1, rdb$relations r2
where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)

but this strikes me as being a very inefficient select statement, does anyone know a better way of achieving what I want to do?

Thanks

Steve

 

[MartijnTonies]

This should help, the following query will list VIEWS only - I guess you can change it to get tables only

select * from rdb$relations where rdb$view_blr is not null



Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[donvittorio]

Thanks Martijn, I hadn't spotted the rdb$view_blr column.
There was also a mistake in my sql (it was excluding the audit_ table, but not the tables being audited). My final sql is thus:

select rdb$relation_name table_name
from rdb$relations
where rdb$system_flag = 0
and rdb$view_blr is null
and rdb$relation_name not like 'AUDIT_%'
and rdb$relation_name not in
(select r2.rdb$relation_name
from rdb$relations r1, rdb$relations r2
where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)
order by rdb$relation_name