sql question...

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

Other related posts: