| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Intersect used in stored procedure
I am really stuck and confused. I've written a pl/sql procedure which
executes a series of insert statements. All of the queries run fine from
the worksheet, but when I try to compile I receive a MSG-00072 error
that the package body has compilation errors. If I paste the contents of
the package body into the the SQL worksheet, all the queries run without
error. Two of the insert queries use INTERSECT to combine two result
sets. If they are removed, the package body compiles without complaint.
I can not see why the INTERSECT should be causing a problem since the run fine on their own. Is there a trick here that I'm not aware of? Any help will be greatly appreciated. I've attached the program to the bottom of this message for those who want to see the syntax. The queries are a bit nasty.
CODE FOLLOWS:
create or replace package xtab13 as
procedure clear;
procedure build;
end;
/
create or replace package body xtab13 as
procedure clear as
begin
/* clear temp table */
delete from s_w2byw1 where username = user;
commit;
end clear;
procedure build as
begin
/* Eligible */
/* Eligible: Children with a wave 1 interview by a family member who was
eligible */
insert into s_w2byw1
select user, substr(a.id,7,2), 'C' "Type",'Eligible', count(*)
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%C' and b.id like '%C' and c.id like '%P'
group by substr(a.id,7,2);
/* Eligible Parents */
/* same as with children */
insert into s_w2byw1
select user, substr(a.id,7,2), 'P' "Type",'Eligible', count(*)
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%P' and b.id like '%P' and c.id like '%C'
group by substr(a.id,7,2);
/* Eligible Families */
insert into s_w2byw1
select user,cohort, 'Both' "Type", 'Eligible', count(*) from
(select substr(a.id,7,2) cohort, substr(a.id,1,5) subid
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%P' and b.id like '%P' and c.id like '%C'
INTERSECT
select substr(a.id,7,2) cohort, substr(a.id,1,5) subid
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%C' and b.id like '%C' and c.id like '%P'
)
group by cohort;
/* Completed */
/* Children with wave 2 interviews by wave 1 cohort*/
insert into s_w2byw1
select user, substr(a.id,7,2), 'C' "Type",'Completed', count(*)
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and b.capa in ('1','3','5') and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5) and a.id like '%C' and b.id like '%C' and c.id like '%P' group by substr(a.id,7,2);
/* Parents with wave 2 interviews by wave 1 cohort */
insert into s_w2byw1
select user, substr(a.id,7,2), 'P' "Type",'Completed', count(*)
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and b.capa in ('1','3','5') and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5) and a.id like '%P' and b.id like '%P' and c.id like '%C' group by substr(a.id,7,2);
/* Families */
insert into s_w2byw1
select user, cohort, 'Both' "Type", 'Completed', count(*) from
(select substr(a.id,7,2) cohort, substr(a.id,1,5) subid
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and b.capa in ('1','3','5')
and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%P' and b.id like '%P' and c.id like '%C'
INTERSECT
select substr(a.id,7,2) cohort, substr(a.id,1,5) subid
from s_trakmain a, s_trakmain b, s_trakmain c
where (a.capa in ('1','3','5') or c.capa in ('1','3','5'))
and a.cs not in ('62','63')
and substr(a.id,9,1)='1' and substr(c.id,9,1)='1'
and substr(b.id,9,1)='2' and b.cs <>'62' and b.capa in ('1','3','5')
and
substr(a.id,1,5)=substr(b.id,1,5) and substr(a.id,1,5)=substr(c.id,1,5)
and a.id like '%C' and b.id like '%C' and c.id like '%P')
group by cohort;
commit;
end build;
end xtab13;
/
Received on Thu Apr 08 1999 - 14:56:02 CDT
 |
 |