Re: Tricky SQL statement

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