Re: Self-Join combined with Outer Join?

Dear David,

Thank you! That worked perfectly! Of course, now I've got to de-simplify all my data and make sure it still functions as expected, but I think it should :)

Thanks to you and everyone else!
Cheers,
m

fitzjarrell_at_cox.net wrote:
> r13l24r2_at_gmail.com wrote:
> > I guess this is probably a simple problem, but I can't for the life of
> > me figure it out. Say I have the following table:
> >
> > job_id batch_id action date
> > ------------ ------------- ------------- --------
> > 1 1 sent 1/1/01
> > 2 1 received 1/2/01
> > 3 2 sent 1/3/01
> > 4 2 received 1/4/01
> > 5 3 sent 1/5/01
> >
> >
> > I want to write a query that will display as follows:
> >
> > batch_id sent received
> > ------------ ------------ ------------
> > 1 1/1/01 1/2/01
> > 2 1/3/01 1/4/01
> > 3 1/5/01
> >
> > I can self-join the table so that it gives me a query displaying
> > batch_id 1 and 2 no problem. I just can't make it display rows for
> > which there is no received action.
> >
> > Any thoughts?? I'm desperate!!
>
> Given your data:
>
>
> JOB_ID BATCH_ID ACTION PROC_DATE
> ---------- ---------- ------------ ---------
> 1 1 sent 01-JAN-01
> 2 1 received 02-JAN-01
> 3 2 sent 03-JAN-01
> 4 2 received 04-JAN-01
> 5 3 sent 05-JAN-01
>
> the following query returns the desired result set:
>
> 1 with col_generator as (
> 2 select j1.batch_id,
> 3 decode(j1.action, 'sent', j1.proc_date) sent,
> 4 decode(j2.action,'received', j2.proc_date) received
> 5 from jobtest j1, jobtest j2
> 6 where j2.batch_id (+)= j1.batch_id
> 7 )
> 8 select batch_id, max(sent) sent, max(received) received
> 9 from col_generator
> 10 group by batch_id
> 11* order by 1
> SQL> /
>
> BATCH_ID SENT RECEIVED
> ---------- --------- ---------
> 1 01-JAN-01 02-JAN-01
> 2 03-JAN-01 04-JAN-01
> 3 05-JAN-01
>
> SQL>
>
> Possibly Jonathan Lewis can improve on this, but it does work and, if
> the only two values for ACTION are 'sent' and 'received' you should
> have your answer.
>
>
> David Fitzjarrell
Received on Tue Aug 01 2006 - 11:04:09 CDT