Re: any ideas on how to improve this query

On 22 Jun 2005 12:27:40 -0700, "lou_nyc" <lnlx_at_aol.com> wrote:

>SELECT *
>FROM (
>SELECT ssl_user_code,
> ssl_user_code ssl,
> i.container,
> out_date,
> cl.code LENGTH_CODE,
> out_trucker_code,
> decode ((SELECT ih.in_date
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.in_date
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_DATE,
> decode ((SELECT ih.in_trucker_code
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.in_trucker_code
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_TRUCKER_CODE,
> decode ((SELECT ih.chassis
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.chassis
> FROM inv_containers ic
> WHERE ic.container = i.container)) IN_CHASSIS
> FROM HIS_containers i,
> container_masters cm,
> tml_container_lhts clht,
> tml_container_lengths cl
> WHERE i.chassis IS NULL AND
> i.out_mode = 'T'
> -- i.out_date BETWEEN to_date ('1-JAN-2005','DD-MON-YYYY') AND
>to_date ('01-FEB-2005','DD-MON-YYYY')*/
>-- AND SSL_USER_CODE = 'ACL'
> -- AND ((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> -- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS'))
> -- OR
> -- (IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> -- AND TO_DATE('01-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS')))
> AND
> cm.container = i.container AND
> cm.lht_code = clht.code AND
> clht.length_code = cl.code AND
> decode ((SELECT ih.container_id
> FROM his_containers ih
> WHERE ih.container = i.container
> AND ih.container_id > i.container_id
> AND ih.container_id = (SELECT MIN(ihh.container_id)
> FROM his_containers ihh
> WHERE ihh.container_id >
>i.container_id
> AND ihh.container =
>i.container)), NULL,
> (SELECT ic.container_id
> FROM inv_containers ic
> WHERE ic.container = i.container)) IS NOT NULL
>)
>WHERE
>SSL_USER_CODE = 'ACL'
>AND
>((OUT_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS'))
> OR
> (IN_DATE BETWEEN TO_DATE('01-MAR-05 00:00:00', 'DD-MON-RR
>HH24:MI:SS')
> AND TO_DATE('31-MAR-05 23:59:59', 'DD-MON-RR
>HH24:MI:SS')))
>and in_date is null

Convert the bunch of correlated inline views into *ONE* join.

--
Sybrand Bakker, Senior Oracle DBA