Re: Help with SQL

"Ranga Chakravarthi" <ranga_at_cfl.rr.com> wrote in message news:<cls_7.281375$oj3.53755809_at_typhoon.tampabay.rr.com>...
> SELECT e.equipid, e.moldno, e.startrun, e.endrun,
> sum(nvl(f.fails, 0)) fails
> FROM equip e, fail f
> WHERE f.equipid (+) = e.equipid
> AND f.moldno (+) = e.moldno
> GROUP BY e.equipid, e.moldno, e.startrun, e.endrun
> /
> "John Kramer" <john_at_ekramer.net> wrote in message
> news:fbcef4df.0201071557.302c2ad0_at_posting.google.com...
> > I have two tables that are joined by five fields, though I've
> > simplified the joins down to two for the example below. The first
> > table holds equipment data (EQUIP), and the second (FAIL) holds data
> > that reflects failures that occured in the production on the equipment
> > stored in EQUIP. The EQUIP table stores the times that the equipment
> > was run, and the FAIL table show aggregated data for time periods when
> > there were failures on the equipment. Here's the problem I'm having:
> >
> > EQUIP Table FAIL Table
> > EquipID MoldNo StartRun EndRun EquipID MoldNo Start End
> > Fails
> > 1 2 13:00 15:00 1 2 13:00
> > 13:05 2 2 2 14:00 15:00 1 2
> > 13:05 13:10 3
> > 1 2 13:10
> > 13:15 2
> > 1 2 13:15
> > 13:20 3
> > 1 2 13:20
> > 13:25 2
> > 1 2 13:25
> > 13:30 3
> > ***The Start and End columns in the EQUIP table represent the time
> > period that the equipment was run.
> > ***The Start and End columns in the FAIL table represent 5 minute
> > increments for recording failures
> > *A record can only exist in the FAIL table if there is a record in the
> > EQUIP table for the same time period, but no record necessarily exists
> > in the FAIL table if one exists in the EQUIP table. In other words, a
> > record is written into the FAIL table only when a failure occurs. If
> > no failure occurs, no record is written.
> >
> > I'd like to get a result set that displays all equipment used during a
> > time period (lets say between 13:00 and 15:00 for this day) and the
> > sum quantity of fails that occurred on that equipment during the
> > sepcific time period that it was run. If there were no fails on that
> > equipment for that time period that it was run, I'd like it to show
> > that there were no errors. Here's the resultset I'd hope to return for
> > the previous data sets:
> >
> > EquipID MoldNo Start End Fails
> > 1 2 13:00 15:00 15
> > 2 2 14:00 15:00 0
> >
> > Everything I've tried will only records from the EQUIP table that have
> > matching records in the FAIL table. I just can't see how to get this
> > to work the way I'd like to. Do I need to use PL/SQL to accomplish
> > this?
> >
> > Your help and expertise is greatly appreciated.
> >

hi milly
WISH U A HAPPY NEW YEAR there are two reasons for the situation. one is that there are doc numbers in spp table which are not present in tow table. and the select query used to find unmatched records is worng coz
select * from spp_test
 where doc not in (select doc

                 from spp);

what you get from the above query is all those records which are not in the spp table but which are in the test table which is obviously zero i.e as you have inserted the records from spp table into test but what you need for finding unmatched records is all those records which are in spp table but which are not in test table then the select query is

SELECT * FROM SPP WHERE DOC NOT IN (SELECT DOC FROM SPP_TEST) the above query will surely give non zero records.

please contact me if you have further doubts. regards

Certified DBA
RAGHU Received on Wed Jan 09 2002 - 07:36:52 CET