Hi,
I am having a cursor with 4 unions in which i am using a count query as a sub_query to check the count of a column from one table is less or equal to the count of a column from another table.But when i compile i am getting the following error:
The query is as follows:
open c2_ref for
select distinct(apd.activity_id) ,'A',is_fixed from activity_predecessor_det apd,project_plan_det ppd,activity_master am
where am.activity_id = apd.activity_id and
ppd.activity_id=apd.pred_activity_id and apd.sub_activity_id is null and ppd.prospect_id=1
and ((apd.project_id is null and apd.prospect_id is null) or apd.prospect_id = 1)
and (select count(pred_activity_id) from activity_predecessor_det
where activity_id=apd.activity_id)<=(select count(activity_id) from project_plan_det
where prospect_id = 1 and activity_id in (select pred_activity_id from activity_predecessor_det
where pred_activity_id in (select pred_activity_id from activity_predecessor_det where activity_id=apd.activity_id)))
union
select distinct(apd.activity_id) ,'A',is_fixed from activity_predecessor_det apd,project_plan_det ppd,activity_master am
where am.activity_id = apd.activity_id and
ppd.sub_activity_id=apd.pred_sub_activity_id and apd.sub_activity_id is null and ppd.prospect_id=1
and ((apd.project_id is null and apd.prospect_id is null) or apd.prospect_id = 1)
and (select count(distinct(pred_sub_activity_id)) from activity_predecessor_det
where activity_id=apd.activity_id)<=(select count(sub_activity_id) from project_plan_det
where prospect_id = 1 and sub_activity_id in (select pred_sub_activity_id from activity_predecessor_det
where pred_sub_activity_id in (select pred_sub_activity_id from activity_predecessor_det where activity_id=apd.activity_id)))
union
select distinct(apd.sub_activity_id) ,'SA',is_fixed from activity_predecessor_det apd,project_plan_det ppd,sub_activity_master sam
where sam.sub_activity_id = apd.sub_activity_id and
ppd.activity_id=apd.pred_activity_id and apd.activity_id is null and ppd.prospect_id=1
and ((apd.project_id is null and apd.prospect_id is null) or apd.prospect_id = 1)
and (select count(distinct(pred_activity_id)) from activity_predecessor_det
where sub_activity_id=apd.sub_activity_id)<=(select count(activity_id) from project_plan_det
where prospect_id = 1 and activity_id in (select pred_activity_id from activity_predecessor_det
where pred_activity_id in (select pred_activity_id from activity_predecessor_det where sub_activity_id=apd.sub_activity_id)))
union
select distinct(apd.sub_activity_id) ,'SA',is_fixed from activity_predecessor_det apd,project_plan_det ppd,sub_activity_master sam
where sam.sub_activity_id = apd.sub_activity_id and
ppd.sub_activity_id=apd.pred_sub_activity_id and apd.activity_id is null and ppd.prospect_id=1
and ((apd.project_id is null and apd.prospect_id is null) or apd.prospect_id = 1);
and (select count(distinct(pred_sub_activity_id)) from activity_predecessor_det
where sub_activity_id=apd.sub_activity_id)<=(select count(sub_activity_id) from project_plan_det
where prospect_id = 1 and sub_activity_id in (select pred_sub_activity_id from activity_predecessor_det
where pred_sub_activity_id in (select pred_sub_activity_id from activity_predecessor_det where sub_activity_id=apd.sub_activity_id)));
The above query if I execute seperately its executing but if i put in a cursor its giving an error.
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count c
