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

From: yossarian <yossarian99_at_operamail.com>
Date: Fri, 11 May 2007 11:13:07 +0200
Message-ID: <46443374$0$20804$5fc30a8@news.tiscali.it>

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. Received on Fri May 11 2007 - 04:13:07 CDT