All,
I am having problems creating a query/s and I am really in need of
some assistance. I have 3 tables storing information about product
defects. Each table has a PK uniquely identifying the defect, and the
PK is the same in each table (FIND_ID). Without going into the details
of why the tables are created so, (which is beyond my control) they
can be summarised below. I will describe as best I can!!
TABLE_1
FIND_ID
WORK_STATION_ID
DEFECTIVE_SUBSYSTEM
DEFECT_SYMPTOM_CODE
PROBLEM_TYPE
LOG_DATE
TABLE_2
FIND_ID
WORK_STATION_ID
ACTION_TAKEN
DEFECT_CAUSE
TABLE_3
FIND_ID
WORK_STATION_ID
DEFECTIVE_PART
DEFECTIVE_CONNECTOR
I am dealing only with the last 70 days information at any one time,
(about 400 records) and have created a view which holds distinct
defects (not including FIND_ID and LOG_DATE which make them unique).
The view holds about 100 records.
VIEW_1
WORK_STATION_ID
DEFECTIVE_SUBSYSTEM
DEFECT_SYMPTOM_CODE
PROBLEM_TYPE
DEFECTIVE_PART
DEFECTIVE_CONNECTOR
DEFECT_CAUSE
ACTION_TAKEN
Ok. My problem is that I need to work out and record how many of those
100 distinct defects occurred on EACH of the last 10 days (SYSDATE-1,
SYSDATE-2 etc) using the LOD_DATE from TABLE_1.
I need to either create a new view displaying this information,
appended it to the existing view, or run it as a query or PL/SQL
stored proc. I know that this is derived data and that 'officially' I
shouldn't store it in the database, but I believe manipulating it on
the client to calculate the figures would be extremely time consuming.
All suggestions, criticisms, and comments are extremely welcome.
Many thanks and Best Regards
Matt Griffiths
Matt.Griffiths_No_Spam__at_dial.pipex.com
remove "_No_Spam_" to email directly
Using Oracle 7.3 for NT
Client Development using VB5
Received on Tue Apr 07 1998 - 00:00:00 CDT