Re: Tricky SQL statement

Sorry I wasn't available to reply, but Charles has it exactly. By pivoting the traveler table, you create something that can be directly joined to your work order table. This doesn't require reformatting anything; you use a common table expression (CTE) to create the pivoted traveler table, and then join from the work order table to that table.

Given an hour or so, I could probably come up with a decent pivot. It might take two CTEs depending on how your sequence numbers are specified. If they are always sequential with a fixed increment, it's easy. If not, I'd probably use one CTE to normalize the sequence numbers in the traveler table then a second to pivot the normalized table. Then I'd join that to the work order file as Charles suggests. What's really nice about using a CTE is that it's easy to rename the fields in the CTE to match the work order file so that you can use the JOIN USING syntax, which expects matching field names.

Joe

That should have been:

select a.wo#, b.trav#, a.proc1, a.proc2, <....>
from wo A inner join trav_pivot B
using (proc1, proc2, proc3, <....>)

Charles

On Wed, May 26, 2010 at 12:34 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

On Wed, May 26, 2010 at 11:55 AM, James Rich <james@xxxxxxxxxxx> wrote:

On Wed, 26 May 2010, Charles Wilt wrote:

Ok, but I don't understand how doing so helps. Maybe I've just had too many
late nights and I'm missing something obvious?

Let's say you pivot the trav data...

Then to find the matches all you need is a simple sql
select a.wo#, b.trav#, a.proc1, a.proc2, <....>
from wo A inner join trav B
using (proc1, proc2, proc3, <....>)

HTH,
Charles