Re: Tricky SQL statement

[Charles Wilt <charles.wilt@xxxxxxxxx>]

On Wed, May 26, 2010 at 1:26 AM, James Rich <james@xxxxxxxxxxx> wrote:
> > That being said, I'm still not sure how I'd go about solving your
> > problem, although my first inclination would be to convert one file to
> > match the format of the other, and then do a join.  It would take a
> > little more time to try and figure out exactly how to do that, but the
> > goal would be to convert the TRAV# table to a table where the fields
> > would be 123, 1.01, 1.02, 1.03.  That's doable, I think.  Once you've
> > got that, it's relatively simple to compare the WO# table to the newly
> > reformatted TRAV# table.
>
> Changing the layout of the work order file is not an option, though
> changing the layout of the traveler procedure file is possible.  Though I
> am very disinclined to do so because I don't want to perpetuate the 15
> procedure limit that the work order file currently has.  I'd prefer to
> keep the header/detail relationship that the traveler and traveler detail
> files currently have.

Just to clarify, I don't believe Joe is telling you that you have to
change the physical format of one of the files.

Instead, consider creating a view over one of the files so that you'd
have the data from both tables available in matching formats...

You can have either a view that pivots the travel table, or a view
that normalizes the work order table.
Pivot view
create view trav_pivot as
 (select trav#,
           max(case seq# when 1 then proc else 0) as proc1,
           max(case seq# when 2 then proc else 0) as proc2,
           max(case seq# when 3 then proc else 0) as proc3,
<...>
  group by trav#
)

Normalize work order...
create view wo_normal as
(select wo#, proc1 as proc
 from wo
 where proc1 <> 0
 UNION ALL
 select wo#, proc2 as proc
 from wo
 where proc2 <> 0
 UNION ALL
 select wo#, proc3 as proc
 from wo
 where proc3 <> 0
 <...>
)


You might experiment with both way to see which performs better.  Note
that since DB2 for i doesn't have an pivot functionality built in,
your pivot view would have to have a static number of columns.
(Though I have seen some utilities that will dynamically create the
pivot. http://www.itjungle.com/fhg/fhg052505-story01.html )  The
static nature may be alright as you'll need to match it the static 15
columns in the work order table.

HTH,
Charles