Re: Tricky SQL statement

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