• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Count query in oracle cursors

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Isn't the syntax for creating cursors in Oracle the following?
DECLARE CURSOR cursor_name IS
SELECT * from tableA

Mark
 
Mark Spritzler
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Check out here.
Mark
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi !
what i think is that u r missing or applying more brackets ().
count them and close all.
cover the quries with in bracket and then apply UNION
in the last query i think u have put in extra bracket.
bye
sachin
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic