× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.