Re: Tricky SQL statement

[CRPence <CRPbottle@xxxxxxxxx>]

On 26-May-2010 09:33, James Rich wrote:
> On Wed, 26 May 2010, CRPence wrote:
> > On 25-May-2010 21:27, James Rich wrote:
> > > I need to come up with an SQL statement that will compare a
> > > set of values in one tables and find a matching set of rows
> > > in another table. The relationships go like this:
> > >
> > > A work order contains 15 fields defined as zoned 4.2 that
> > > are called procedure numbers.
> > >
> > > A traveler document has a many to one relationship with the
> > > traveler procedure table. The relevant fields from this
> > > table are traveler ID number and procedure number, defined
> > > the same as in the work order table.
> > >
> > > Given a work order number, I need to find all traveler ID
> > > numbers whose related traveler procedure table records match
> > > those from the work order table, including being in the same
> > > sequence.
> > >
> > > Or in lovely ASCII art:
> > >
> > >  __________________________________
> > >  | WO# | PROC 1 | PROC 2 | PROC 3 |
> > >  ----------------------------------
> > >  |  1  |  1.01  |  1.02  |  1.03  |
> > >  ----------------------------------
> > >
> > > matches:
> > >
> > >  __________________<ed>_
> > >  | TRAV# | PROC | Seq  |
> > >  ----------------------+
> > >  |  123  | 1.01 |   1  |
> > >  ----------------------+
> > >  |  123  | 1.02 |   2  |
> > >  ----------------------+
> > >  |  123  | 1.03 |   3  |
> > >  -----------------------
> > >
> > > So my SQL statement should return "123" as the traveler ID
> > > number that matches the procedures from the given work order
> > > number.
> > >
> > > I'm not super great at constructing SQL queries, so this
> > > might be super simple, but I haven't been able to come up
> > > with anything that works. Please help me out :)
>
> > Another post clarified that there is a /sequence-#/ in the
> > Traveler Procedures file [as <ed>ited above] to correlate with the
> > respective column numbers in the Traveler WorkOrders file....
> > However, I am wondering how to detect when the procedures for the
> > selected work-order-number exceed the count of procedure for a
> > particular traveler-ID-number.? In the given example of a match,
>
> If the count of the non-zero fields in the work order differs from the
> number of records in the traveler procedure file then they do not match.
>
> > are all of the columns Proc-4 through Proc-15 the NULL value?
>
> No, they are zero.
>
> > If for example for the given work-order-number=1, if that row had
> > included Proc-4=1.04, then I presume that row would no longer be a
> > match for the set of TRAV#=123?
>
> Correct.
>
> > Finally just to be sure, is each Proc-# column assured to
> > correlate directly to the same #-value in the Sequence-#; e.g.
> > can Proc-3 be NULL, such that Proc-4 then represents the
> > Sequence#=3?
>
> There will never be a zero procedure number field followed by a non-zero
> procedure number field.
>
> I sorry I didn't think of these possibilities earlier. You are all so
> very thorough! Thanks!

  The following is written with the idea that "the given work order 
number" would be used to select the matching "traveler ID numbers", 
and hopefully it actually functions as intended.  Each NTE could be 
replaced by a generic VIEW with the selection against the VIEW in a 
revision of the existing NTE.

<code>

 create table wo
  (wonr numeric(1, 0) ,p01 numeric(3, 2)
  ,p02  numeric(3, 2) ,p03 numeric(3, 2)
  ,p04  numeric(3, 2) /* up to p15 */
  )
 ;

 insert into wo (wonr, p01, p02, p03) values
    (1, 1.01, 1.02, 1.03), (2, 1.01, 1.02, 2.22)
   ,(3, 1.01, 2.22, 3.33), (4, 4.44, 3.33, 2.22)
   ,(5, 1.03, 1.02, 1.01), (6, 6.66, 1.01, 1.02)
 ;

 update wo set p04=0
 ;

 create table td
  (trnr numeric(3, 0), p numeric(3, 2), seq numeric(2))
 ;

 insert into td values
     (666, 6.66, 1),(666, 5.55, 2),(666, 1.03, 3)
    ,(678, 6.66, 1),(678, 1.02, 2),(678, 1.03, 3)
    ,(123, 1.01, 1),(123, 1.02, 2),(123, 1.03, 3) /* just right */
    ,(012, 1.01, 1),(012, 1.02, 2) /* too few */
    ,(321, 1.03, 1),(321, 1.02, 2),(321, 1.01, 3)
    ,(234, 1.01, 1),(234, 1.02, 2),(234, 1.03, 3)
    ,(234, 1.04, 4) /* too many */
 ;

  select t.trnr
  from             td t
   left outer join (
      select wonr,( 1) i,p01 p from wo where p01<>0 and wonr=:w
     union all
      select wonr,( 2) i,p02 p from wo where p02<>0 and wonr=:w
     union all
      select wonr,( 3) i,p03 p from wo where p03<>0 and wonr=:w
     union all
      select wonr,( 4) i,p04 p from wo where p04<>0 and wonr=:w
     /* repeat to (15) & p15 */
                   )  w
   on   t.p=w.p
    and t.seq=w.i
   group by t.trnr
   having count(*)=count(i)
      and count(*)=(select case when p01<>0 then 1 else 0 end
                         + case when p02<>0 then 1 else 0 end
                         + case when p03<>0 then 1 else 0 end
                         + case when p04<>0 then 1 else 0 end
                         /* repeat up to p15 */
                     /* (15-ifnull(p01,1)-ifnull(p02,1)...) */
                    from wo where wonr=:w)

</code>

Regards, Chuck