Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query rewrite help needed

Re: Query rewrite help needed

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 10 Mar 2003 05:43:49 -0800
Message-ID: <F001.005645D2.20030310054349@fatcity.com>


Try something like this:

select a.OP_FL_PLAN_KEY,

a.FLIGHT_NO,
a.ORIGIN,
a.DESTINATION,
a.SEG_DEP_DATE_TIME,
a.SEG_ARR_DATE_TIME,
a.DEP_DAY_CHG,
a.ARR_DAY_CHG,

DECODE(a.ac_type, 'JET', c.AC_TYPE, a.AC_TYPE),
a.AC_GRP, b.exp_lat_date_time,
b.imp_toa_date_time,
b.spl_code

from op_fl_plan a, op_fl_service_times b, op_restriction d,
op_restriction_rq e
where a.op_fl_plan_key = b.op_fl_plan_key
and a.op_fl_plan_key = e.op_fl_plan_key
and b.op_fl_service_times_key = d.op_fl_service_times_key
and a.op_fl_plan_key (+)= c.op_fl_plan_key            -- outer join
and a.ac_type <> 'JET'
and b.exp_lat_date_time >= :eldt
and b.imp_toa_date_time <= :itdt
and b.spl_code in(:spl_code1, :spl_code2)
and b.service = :srv and a.origin = :o and a.destination = :d
and substr(a.flight_no, 1, 2) = :flight_no
and a.ac_grp in(:ac_grp)
and trunc(a.seg_dep_date_time) = :seg_dep_date
and e.spl_code in(:spl_code1, :spl_code2)
and d.spl_code not in(:spl_code1, :spl_code2)
and d.position in('E', 'I')


Igor Neyman, OCP DBA
ineyman_at_perceptron.com

> Hi List,
>
> I have the below query which is very long and it contains an UNION
> clause as the query has to select ac_type from op_ac_type table if
> op_fl_plan.op-ac_type = 'JET' while it should select op_ac_type from
> op_fl_plan table if op_fl_plan.ac_type = 'JET'. Is it possible to
rewrite
> the query using DECODE statement or CASE statement or self joins to return
> the same result set? If so, please let me know the same. Any help in
this
> regard is very much appreciated.
>
> Thanks and Regards,
>
> Ranganath
>
>
> select a.OP_FL_PLAN_KEY,
> a.FLIGHT_NO,
> a.ORIGIN,
> a.DESTINATION,
> a.SEG_DEP_DATE_TIME,
> a.SEG_ARR_DATE_TIME,
> a.DEP_DAY_CHG,
> a.ARR_DAY_CHG,
> a.AC_TYPE,
> a.AC_GRP, b.exp_lat_date_time,
> b.imp_toa_date_time,
> b.spl_code from
> op_fl_plan a, op_fl_service_times b,
> op_restriction d,
> op_restriction_rq e
> where a.op_fl_plan_key = b.op_fl_plan_key
> and a.op_fl_plan_key = e.op_fl_plan_key
> and b.op_fl_service_times_key = d.op_fl_service_times_key
> and a.ac_type <> 'JET'
> and b.exp_lat_date_time >= :eldt
> and b.imp_toa_date_time <= :itdt
> and b.spl_code in(:spl_code1, :spl_code2)
> and b.service = :srv and a.origin = :o and a.destination = :d
> and substr(a.flight_no, 1, 2) = :flight_no
> and a.ac_grp in(:ac_grp)
> and trunc(a.seg_dep_date_time) = :seg_dep_date
> and e.spl_code in(:spl_code1, :spl_code2)
> and d.spl_code not in(:spl_code1, :spl_code2)
> and d.position in('E', 'I')
> union
> select a.OP_FL_PLAN_KEY,
> a.FLIGHT_NO,
> a.ORIGIN,
> a.DESTINATION,
> a.SEG_DEP_DATE_TIME,
> a.SEG_ARR_DATE_TIME,
> a.DEP_DAY_CHG,
> a.ARR_DAY_CHG,
> c.AC_TYPE,
> a.AC_GRP, b.exp_lat_date_time,
> b.imp_toa_date_time,
> b.spl_code from
> op_fl_plan a, op_fl_service_times b, op_ac_type c,
> op_restriction d,
> op_restriction_rq e
> where a.op_fl_plan_key = b.op_fl_plan_key
> and a.op_fl_plan_key = e.op_fl_plan_key
> and b.op_fl_service_times_key = d.op_fl_service_times_key
> and a.op_fl_plan_key = c.op_fl_plan_key
> and a.ac_type = 'JET'
> and b.exp_lat_date_time >= :eldt
> and b.imp_toa_date_time <= :itdt
> and b.spl_code in(:spl_code1, :spl_code2)
> and b.service = :srv and a.origin = :o and a.destination = :d
> and substr(a.flight_no, 1, 2) = :flight_no
> and a.ac_grp in(:ac_grp)
> and trunc(a.seg_dep_date_time) = :seg_dep_date
> and e.spl_code in(:spl_code1, :spl_code2)
> and d.spl_code not in(:spl_code1, :spl_code2)
> and d.position in('E', 'I')
> WARNING: The information in this message is confidential and may be
legally
> privileged. It is intended solely for the addressee. Access to this
message
> by anyone else is unauthorised. If you are not the intended recipient,
any
> disclosure, copying, or distribution of the message, or any action or
> omission taken by you in reliance on it, is prohibited and may be
unlawful.
> Please immediately contact the sender if you have received this message in
> error. Thank you.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Krishnaswamy, Ranganath
> INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Mar 10 2003 - 07:43:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US