Reports 2.5 Data Model questions

From: Mike McCormick <76046.1057_at_CompuServe.COM>
Date: 1997/07/22
Message-ID: <5r31pc$rjj$1_at_mhafn.production.compuserve.com>#1/1

  Alright, let's see if there are any Reports 2.5 geniuses out there. I have a Master-Detail-Detail relationship in a Data Model that I believe to be horribly inefficient.

  It begins with a Master that does a query on a table called Operations, which is a list of work areas that a part will go to to be made. The detail under Operations is a list of Items. Items can be Tools, Parts, or Instructions needed for that work area to work on the part. The query looks like this:

SELECT 'TOOL' ITEM_TYPE,

       PART_NUMBER,
       OPERATION_NUMBER,
       SEQUENCE_NUMBER

  FROM ITEMS
 WHERE TOOL IS NOT NULL
UNION
SELECT 'PART' ITEM_TYPE,

       PART_NUMBER,
       OPERATION_NUMBER,
       SEQUENCE_NUMBER

  FROM ITEMS
 WHERE CHILD_PART IS NOT NULL
UNION
SELECT 'INST' ITEM_TYPE,

       PART_NUMBER,
       OPERATION_NUMBER,
       SEQUENCE_NUMBER

  FROM ITEMS
 WHERE INSTR_TEXT IS NOT NULL
 ORDER BY 4   Part_number and Operation_Number link this detail to it's master Operation. Since an Item can be only one of the three types, the columns Part_Number, Operation_Number and Sequence_Number denote a unique record.

  Now, to get the details on each of the Items, I have three additional queries like this:

/* Tool Detail Query */

SELECT 'TOOL' TOOL_TYPE,

       I.PART_NUMBER,
       I.OPERATION_NUMBER,
       I.SEQUENCE_NUMBER,
       I.TOOL,
       T.DESCRIPTION,
       T.STORAGE

  FROM ITEMS I, TOOLCRIB.TOOLS T
 WHERE I.TOOL = T.TOOL   The Part and Instruction detail queries are just as ugly. The first four columns are used to link to the Items query above.

  Now here, finally, is the question. Every record retrieved by the Item query is going to run a query on all three detail queries. Only one will return a record, due to the link between ITEM_TYPE and xxxx_TYPE, but all three will still be processed.

  So, is there a way to control which one detail query will be run for each record of the Item query? Or can I get the details through a Forumla Column instead of through a query?

  Also, can I link to the detail query using Rowid instead of Part_number, Operation_Number and Sequence_Number? I tried it, but I kept getting "ORA-01475 Must reparse cursor to change bind variable datatype".

  P.S. In a previous version of this Data Model, I had the Item query and Detail queries melded into a single ugly statement. Unfortunately one of the columns I need to retrieve is a VARCHAR2(2000) and I'd get a "Sort Key Too Long" error whenever the text column was filled with characters.

  P.P.S. This is actually a simplified example of my problem. In reality I have five different types of Items and it takes four columns to denote a unique Item record. This is one slow report!

  Thanks for any advice! Sorry about the length of this thing.     Michael McCormick

-- 


/* If at first you don't succeed, switch to power tools */

Received on Tue Jul 22 1997 - 00:00:00 CEST