Re: Tricky SQL statement

On Tue, 25 May 2010, Joe Pluta wrote:

One quick question: you say the sets (consider the procedures for a work
order to be one set, the procedures for a traveler to be another) have
to match "including being in the same sequence". What sequence is that,
exactly? There is no sequence field. All we have is procedure number,
unless you're considering RRN number. If there really is only procedure
number, then you don't have to worry about sequence, you just have to
worry about the sets being congruent.

Ah - of course I forgot that little detail. The work order file contains 15 fields for the procedure numbers, called WOPROC1 - WOPROC15. The traveler procedure file is instead "normalized" and contains a sequence number field to put the records in the correct order. That's a pretty important detail, I apologize for leaving it out!

I know it doesn't answer your question, but I've found that any SQL
beyond the trivial really benefits from a very thorough understanding of
the business question. The most successful SQL programmers I've met are
very good at analyzing their requirement before they start messing with
the SQL. Those who do not create 5000 character select statements.

Agreed. In this case the business problem is to find any travelers previously created that use the exact same procedures as a given work order. In general, multiple work orders are received from various customers and often those work orders require the same tasks to be accomplished. The traveler is akin to the list of tasks to perform. IF a work order comes in that has the same requirements as a previous order required, we want to find the traveler that was used for that prior work order and use it again.

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.

James Rich

if you want to understand why that is, there are many good books on
the design of operating systems. please pass them along to redmond
when you're done reading them :)
- Paul Davis on ardour-dev