Hi Tom,

I have the following hierarchical query that runs Ok on it's own and also compiles Ok in a stored procedure on "Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta" and produces the desired result.

However on "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production", it runs Ok as a stand-alone query, but it won't compile in a stored procedure.

See the compiler error message in the commented below, on the line that gives the error. Somehow the compiler can't seem to find the type for the field "y_level_id".

Any idea why this might be so?

I have given the table DDL below the query for convenience.

QUERY:

with
x (src_code, proj_code, proj_desc, proj_src_id, core_proj_id, parent_proj_id, x_level_id, is_active)
as ( select s.src_code, j.proj_code, j.proj_desc, j.proj_src_id, j.proj_id core_proj_id, j.parent_proj_id, 0 as x_level_id, j.is_active
from proj j
join src s on (s.src_id = j.proj_src_id)
where ( lower(j.proj_code) like '%p1%'
or lower(j.proj_desc) like '%p1%'
)
),
y (pcodes, proj_src_id, core_proj_id, proj_id, parent_proj_id, y_level_id, is_active)
as ( select
rtrim(j.proj_code) || '/' as pcodes,
j.proj_src_id,
x.core_proj_id,
j.proj_id,
j.parent_proj_id,
x.x_level_id + 1 as y_level_id,
j.is_active
from proj j
join x
on (j.proj_src_id = x.proj_src_id
and j.proj_id = x.parent_proj_id)
union all
select
rtrim(j.proj_code) || '/' || y.pcodes as pcodes,
j.proj_src_id,
y.core_proj_id,
j.proj_id,
j.parent_proj_id,
y.y_level_id + 1 as y_level_id, --- this line gives oracle error: Error: PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
j.is_active
from proj j
join y
on (j.proj_src_id = y.proj_src_id
and j.proj_id = y.parent_proj_id
and j.proj_id <> j.parent_proj_id)
),
m (proj_src_id, core_proj_id, max_level_id)
as (select proj_src_id, core_proj_id, max(y_level_id) max_level_id
from y
group by proj_src_id, core_proj_id
)
select
x.src_code "Src",
x.proj_code "Code",
x.proj_desc "Proj",
case when ((nvl(x.is_active, 1) = 0) or (nvl(y.is_active, 1) = 0) or (nvl(t.is_active, 1) = 0)) then 'Deactivated' else null end "Active",
case
when (x.parent_proj_id = x.core_proj_id) then null -- 'Top Level Project'
when (y.proj_id = y.parent_proj_id) then '//' || rtrim(t.proj_code) || '/' ||rtrim(x.proj_code) -- 'Level 2 Proj'
else '//' || rtrim(t.proj_code) || '/' || y.pcodes || rtrim(x.proj_code) -- 'Deep Proj'
end "Proj Hierarchy"
-- , t.proj_code top_code, y.pcodes, m.max_level_id, x.core_proj_id, x.parent_proj_id x_p_proj_id, y.proj_id y_proj_id, y.parent_proj_id y_p_proj_id
from x
left join m
on (m.proj_src_id = x.proj_src_id
and m.core_proj_id = x.core_proj_id)
left join y
on (y.proj_src_id = m.proj_src_id
and y.core_proj_id = m.core_proj_id
and y.y_level_id = m.max_level_id)
left join proj t
on (t.proj_src_id = y.proj_src_id
and t.proj_id = y.parent_proj_id
and t.proj_id = t.parent_proj_id)
where rownum <= 1000
order by x.src_code, x.proj_code, x.proj_desc;



TABLE DDL:

create table PROJ
(
proj_src_id NUMBER(5) not null,
proj_id NUMBER(10) not null,
proj_code CHAR(20) not null,
proj_desc VARCHAR2(100) not null,
is_active NUMBER(1) default (1) not null,
mod_user VARCHAR2(30) not null,
mod_pc VARCHAR2(30) not null,
mod_date DATE not null,
create_user VARCHAR2(30) not null,
create_pc VARCHAR2(30) not null,
create_date DATE not null,
comments VARCHAR2(4000),
parent_proj_id NUMBER(10) not null,
constraint PK_PROJ primary key (PROJ_SRC_ID, PROJ_ID)
)
organization index;