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

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

Relevant Pages

  • Re: DISTINCTROW
    ... the sql statement is querying 2 tables and 1 stored query. ... but these are not unique indexes. ... tblB does not have primary key and does not have index. ...
    (microsoft.public.access.queries)
  • Re: Create an MS Access Table in VB6
    ... You can run a simple DDL query to ... MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY, ... cn.Execute strsql ' the above sql statement ... create an AutoNumbering Index in my table as a Primary Key. ...
    (microsoft.public.vb.general.discussion)
  • Re: referencing the outermost table from an inner view which is inside a subselect
    ... create table alm ( ... row_numberover (partition by trunc(dat_cam,'month') order by ... all_cod varchar2primary key, ... first query passing to it the values retrieved from all_cod_list instead ...
    (comp.databases.oracle.server)
  • Re: how to find out if there is any primary key in each table
    ... This query will return only those tables that do not have a primary key. ... > What is the command / stored procedure that will tell such information in ... > sql statement. ...
    (microsoft.public.sqlserver.programming)
  • Re: SET ENGINEBEHAVIOR 70?????
    ... One query is quicker to write than two queries, ... this because the id field is the primary key of the parent table. ... sumas Total from Parent join Child on ... > or use two SQL statement ...
    (microsoft.public.fox.vfp.queries-sql)