Re: referencing the outermost table from an inner view which is inside a subselect

From: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: 11 May 2007 08:55:37 -0700
Message-ID: <1178898936.970323.15870@q75g2000hsh.googlegroups.com>

On May 11, 5:13 am, yossarian <yossaria..._at_operamail.com> wrote:
> Hello, Oracle 9.0.1 here.
>
> I have a table which simplified structure is:
>
> create table alm (
> ALL_COD varchar2(7)
> DAT_CAM date,
> primary key (all_cod,dat_cam),
> CAR_CEL number
> ) ;
>
> The following query calculates the mean of the CAR_CEL column for a
> given :all_cod in a given period discarting the highest and the lower
> value for each month (if the values avaliable in a month are 1 or 2 the
> discard does not happen):
>
> select
> avg(val)
> from (
> select
> car_cel val,
> row_number() over (partition by trunc(dat_cam,'month') order by
> car_cel) rwnum,
> count(*) over (partition by trunc(dat_cam,'month'))
> cnt
> from
> alm
> where
> alm.all_cod = :all_cod and
> alm.dat_cam between :date_start and :date_end and
> car_cel is not null
> )
> where (cnt>2 and rwnum>1 and rwnum!=cnt) or (cnt<=2)
> ;
>
> This works perfectly for a single all_cod and a given period.
>
> Now let's suppose I have a table which contains a list of all_cod and
> every all_cod has a different period associated to him:
>
> create table all_cod_list (
> all_cod varchar2(7) primary key,
> date_start date,
> date_end date
> ) ;
>
> This mean that I have to write a SQL statement that cycles through all
> the all_cod values included in the all_cod_list table and calls the
> first query passing to it the values retrieved from all_cod_list instead
> of using bind variables.
>
> Unfortunately a subselect doesn't work, because the references to
> all_cod_list are buried insied an inline view:
>
> SQL> select
> 2 lst.all_cod,
> 3 lst.start_date,
> 4 lst.end_date,
> 5 (
> 6 select
> 7 avg(val)
> 8 from (
> 9 select
> 10 car_cel val,
> 11 row_number() over (partition by trunc(dat_cam,'month')
> order by car_cel) rwnum,
> 12 count(*) over (partition by trunc(dat_cam,'month'))
> cnt
> 13 from
> 14 alm
> 15 where
> 16 alm.all_cod = lst.all_cod and
> 17 alm.dat_cam between lst.start_date and lst.end_date and
> 18 car_cel is not null
> 19 )
> 20 where (cnt>2 and rwnum>1 and rwnum!=cnt) or (cnt<=2)
> 21 ) x
> 22 from
> 23 all_cod_list lst
> 24 ;
> lst.end_date,
> *
> ERROR at line 4:
> ORA-00904: invalid column name
>
> I solved by writing a stored function, but I don't like this solution.
> Any suggestion?
>
> Thank you. Kind regards, Y.

What about something like below (please verify according to your rules):

select
  all_cod, avg(val)
from (
  select
    car_cel val,
    row_number() over (partition by trunc(dat_cam,'month') order by car_cel) rwnum,

    count(*)     over (partition by trunc(dat_cam,'month'))
     cnt

  from
    alm, all_cod_list
  where
    alm.all_cod = :all_cod and
    alm.dat_cam between :date_start and :date_end and     car_cel is not null
)
where (cnt>2 and rwnum>1 and rwnum!=cnt) or (cnt<=2) group by all_cod Received on Fri May 11 2007 - 10:55:37 CDT