SQL Approach, Multiple Tables, UNION?

I'm not sure if this should go to the misc group or this one, so apologies in advance if I've misposted (but please let me know, so I can re-direct this).

IS there a better approach to the following other than using the UNION operator several times? Of course, I would like to avoid functions such as decode or Case in the where clause.... The work environment is a facilities maintenance/management organization.

I have a Work order table, F_WORKORDER. Depending on the nature of the work:

1. a work order record will usually (but not always) be linked to a location table, F_AREAS.
2. in addition, a work order record may or may not also be linked to *one* of six tables, the names of which are generally self-explanatory: F_EQUIPMENT, F_VEHICLES, F_TOOLS, F_ASSETS, F_ENTITY, or F_GROUPS.
3. if linked to one of the tables, the structure of the database is, unfortunately (I have no control over the structure), such that instead of using some system number in a primary/foreign key type link, the link is accomplished using the actual tag number stored for each item in one of the 6 above tables. The application which uses the work orders has a separate column in the F_WORKORDER table which indicates which of the tables to search (I assume the forms, powered by Omnis Studio, of which I know nothing about, uses some sort of code/row processing procedure to bring up the correct data from the correct tables when a work order is displayed on screen).

Thus, the relevant structure of the work order table looks something like:

F_WORKORDER.WO_FU_FK This is the FK for F_AREAS, the location table. Usually populated, but not always.

F_WORKORDER.WO_EQUIP_TAG A numeric which indicates to which of the other 6 tables the work order record may be linked. 0 is F_EQUIPMENT, 1, is F_VEHICLES, etc

F_WORKORDER.WO_TAG_NO The tag number of the actual item in one of the 6 tables to which the work order is linked. The real difficulty here is that there is nothing preventing users in the actual maintenance application to have items from different tables having *exactly* the same tag!

I'm working on an app which allows users to choose from a wide variety of criteria and the app (which is an Access FE) takes the criteria and constructs Oracle SQL which will best retrieve the info. Thus, when a user indicates some criteria which does not specify if the work orders are to be equipment, asset, vehicle, etc based, I need to be able to combine work orders that are associated with just an area (item 1, above) with work orders associated with 6 other tables as well - that's 6 union operators....

If you're still with me, thanks for reading and TIA for any suggestions other than beating up the developers of the maintenance application with which I'm working! 8)

-- 
Tim - http://www.ucs.mun.ca/~tmarshal/
 ^o<  
 /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^  "Want some?" - Ditto