Re: Tricky SQL statement

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

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
> >
> >