need to use a subselect and need help

[sammybee]

Hi All,

Below is my script, I need to find the avg no tot_offers made between the 2 dates as per my subselect, I cannot use the b. and a.vev_event in the subselect, any ideas?

SELECT AVG (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) aver,
MAX (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) maxi,
MIN (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) mini,
AVG (tots.totoffs)
FROM properties,
property_elements,
void_instances,
void_events a,
void_events b, (select oof_pro_refno, count (*) totoffs from organisation_offers where oof_offer_date between
b.vev_event_date and a.vev_event_date

group by oof_pro_refno) tots
WHERE pro_refno = pel_pro_refno(+)
AND pel_ele_code = 'NOBD'
AND pel_numeric_value = 0
AND pel_pro_refno = vin_pro_refno(+)
AND vin_refno = a.vev_vin_refno(+)
AND a.vev_evt_code = 'VOID'
AND vin_refno = b.vev_vin_refno(+)
AND b.vev_evt_code = 'LET'
AND vin_status_start BETWEEN '01-APR-2003' AND '01-APR-2004'
AND a.vev_event_date BETWEEN '01-APR-2003' AND '01-APR-2004'
and vin_pro_refno = tots.oof_pro_refno
ORDER BY pro_propref

Many thanks

Sam

 

[SantaMufasa]

Sam,

I don't have tables and values to be able to test this idea, but can you not move the WHERE references to "b. and a.vev_event_date" for your subquery out of the subquery and into your main WHERE clause, still proferring correct results (as I have done, below)?:

SELECT   AVG (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) aver,
         MAX (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) maxi,
         MIN (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) mini,
         AVG (tots.totoffs)
    FROM properties,
         property_elements,
         void_instances,
         void_events a,
         void_events b,
        (select oof_pro_refno, count (*) totoffs
           from organisation_offers
          [b]/*
          where oof_offer_date
                between b.vev_event_date and
                        a.vev_event_date
          (move subquery WHERE clause down to outer WHERE)*/[/b]
          group by oof_pro_refno) tots
   WHERE pro_refno = pel_pro_refno(+)
     [b]AND tots.oof_offer_date
                between b.vev_event_date and
                        a.vev_event_date[/b]
     AND pel_ele_code = 'NOBD'
     AND pel_numeric_value = 0
     AND pel_pro_refno = vin_pro_refno(+)
     AND vin_refno = a.vev_vin_refno(+)
     AND a.vev_evt_code = 'VOID'
     AND vin_refno = b.vev_vin_refno(+)
     AND b.vev_evt_code = 'LET'
     AND vin_status_start BETWEEN '01-APR-2003' AND '01-APR-2004'
     AND a.vev_event_date BETWEEN '01-APR-2003' AND '01-APR-2004'
     and vin_pro_refno = tots.oof_pro_refno
ORDER BY pro_propref
*****************************************************************************

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:23 (13Dec04) UTC (aka "GMT" and "Zulu"),
@ 13:23 (13Dec04) Mountain Time

 

[SantaMufasa]

Sam,

I knew if I hadn't the data and tables, I'd probably shoot myself in the foot...and I did. My code suggestion above will fail compilation because the subquery does not return "tots.oof_offer_date", which will be necessary in the relocated WHERE clause, "...AND tots.oof_offer_date between b.vev_event_date and a.vev_event_date..."; and if you do include oof_offer_date in the subquery expression list, it will goof up your GROUP SELECT.

Therefore, I retract my post from 13Dec04 @ 20:24 and suggest a replacement of the following Function definition and original-code rework:

Section 1 -- Function definition, "OrgOfferCnt":

Create or replace function OrgOfferCnt
        (pro_refno number, beg_date date, end_date date)
        return number is
    hold_tots     number;
begin
    Select count (*) into hold_tots
           from organisation_offers
          where pro_refno = oof_pro_refno
            and oof_offer_date
                between beg_date and end_date;
    return hold_tots;
end;
/

function created.

Section 2 -- Code rework using function, "OrgOfferCnt":

SELECT   AVG (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) aver,
         MAX (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) maxi,
         MIN (TO_DATE (b.vev_event_date) - TO_DATE (a.vev_event_date)) mini,
         AVG (OrgOfferCnt(vin_pro_refno,b.vev_event_date,a.vev_event_date))
          avg_totals
    FROM properties,
         property_elements,
         void_instances,
         void_events a,
         void_events b
   WHERE pro_refno = pel_pro_refno(+)
     AND pel_ele_code = 'NOBD'
     AND pel_numeric_value = 0
     AND pel_pro_refno = vin_pro_refno(+)
     AND vin_refno = a.vev_vin_refno(+)
     AND a.vev_evt_code = 'VOID'
     AND vin_refno = b.vev_vin_refno(+)
     AND b.vev_evt_code = 'LET'
     AND vin_status_start BETWEEN '01-APR-2003' AND '01-APR-2004'
     AND a.vev_event_date BETWEEN '01-APR-2003' AND '01-APR-2004'
ORDER BY pro_propref

Please try this and let us know how it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:59 (13Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:59 (13Dec04) Mountain Time