Re: Help with Oracle SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 02 Dec 1998 22:15:07 GMT
Message-ID: <3669ba07.31077917@inet16.us.oracle.com>

On Wed, 2 Dec 1998 11:39:12 -0800, "Doug Stone" <dstone_at_res-q.com> wrote:

>Hi. This SQL executes against SQLA, but not Oracle.
>
>Does anyone see why Oracle does not like this view?
>
>CREATE VIEW bbv_proj_avg_dly_vol_1
> (bb_group_id,
> activity_descr_id,
> hiearchy_level_nbr,
> day_nbr,
> sum_val ) AS
>select C.bb_group_id,
> C.activity_descr_id,
> C.Hiearchy_level_nbr,
> A.day_nbr,
> ROUND(SUM(A.vol),2) /
> (select COUNT (*)-1
> from bb_group_shift_r E,
> bb_group_activity_std_r F
> where E.bb_group_id = F.bb_group_id) sum_val
>from bb_group_proj_volume A,
> shifts B,
> bb_group_activity_std_r C,
> bb_group_shift_r D
>where (C.bb_group_activity_std_r_id = A.bb_group_activity_std_r_id)
> and (C.bb_group_shift_r_id = D.bb_group_shift_r_id)
> and (D.shift_id = B.shift_id)
> and (C.hiearchy_level_nbr <> 0)
> and (B.shift_type = 'I')
>group by C.bb_group_id,
> C.activity_descr_id,
> C.Hiearchy_level_nbr,
> A.day_nbr;
>

You can't select in the select clause. You can create a function instead.

eg.

create or replace function the_sum return number as   n number;
begin
  select COUNT (*)-1
    into n
    from bb_group_shift_r E,

         bb_group_activity_std_r F
   where E.bb_group_id = F.bb_group_id
  return n;
end;
/

and your query will now be

CREATE VIEW bbv_proj_avg_dly_vol_1
 (bb_group_id,
 activity_descr_id,
 hiearchy_level_nbr,
 day_nbr,
 sum_val ) AS
select C.bb_group_id,

   C.activity_descr_id,
   C.Hiearchy_level_nbr,
   A.day_nbr,

   ROUND(SUM(A.vol),2)/the_sum sum_val
from bb_group_proj_volume A,

      shifts B,
...

> Thank you,
>Doug
>
>

--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 02 1998 - 16:15:07 CST