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

NET_HOV_BASE.crm_interaction i,

NET_HOV_BASE.crm_work cw, 

NET_HOV_BASE.company_survey_work sw 


                                              and cw.work_id =

                                              and trunc(i.ended)=



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!



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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Other related posts: