Tim Marshall wrote:
>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)
>
>
Given the data you present I'd be thinking of using in-line views ...
not unions. And definitely native dynamic SQL.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sun Sep 07 2003 - 10:34:33 CDT
