sql question...

From: Chris Stephens <ChrisStephens_at_Affina.com>
Date: Fri, 27 Aug 2004 10:25:18 -0500
Message-ID: <E07BCCA62696AB40BE1267A7419A281A88CC8A@apollo.affina.net>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Aug 27 2004 - 11:47:23 CDT