sql question...
- From: "Chris Stephens" <ChrisStephens@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Aug 2004 10:25:18 -0500
Found this view definition in a reporting database:
select station_code,creative_code,dnis, b.Phone_number,dma_code,
c.dma_clean_name, d.CallDate, a.fromdate,a.todate
from NET_HOV_BASE.company_dnis_assignments a,
NET_HOV_BASE.crm_dnis b,
(select dma,dma_clean_name from NET_HOV_BASE.company_dma_names
union select to_number(null),null from dual) c,
(select distinct to_date(trunc(ended)) as CallDate from
NET_HOV_BASE.crm_interaction ) d
where b.numbr = a.dnis
and c.dma (+) = a.dma_code
and b.IS_ACTIVE = 'T'
and trunc(d.CallDate) >= trunc(a.FROMDATE)
and trunc(d.CallDate) <= trunc(a.ToDate)
and local_national <> 'P'
and a.deletedate is null
and (a.station_code,a.dnis) not in (select sw.station_code,i.dnis
from
NET_HOV_BASE.crm_interaction i,
NET_HOV_BASE.crm_work cw,
NET_HOV_BASE.company_survey_work sw
where
i.interaction_id=cw.interaction_id
and cw.work_id =
sw.work_id
and trunc(i.ended)=
trunc(d.CallDate));
It runs forever and is part of a report that takes 2 hours to run. I
need to take a look at the report that utilizes this but for now....I
can't for the life of me see any reason to union company_dma_names with
null values. Maybe I just need another cup of coffee but isn't the
'null row' automatically produced in the outer join?
I hope I'm not the only one working for an understaffed company that has
trouble staying on top of things!
Chris
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
