Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

Getting Error in Oracle 8i Cursor using Count in the Subquery

Dear All,

     This is pradeep.Iam getting the following error while executing following script.In the script i have cursor using subquery with count.Here iam script and Error Please help.


-----------------------This is the Error iam getting--------------------------

((SELECT count(*) Cnt_OT FROM tpt_ext_product_catalog_temp y
             *
ERROR at line 39:
ORA-06550: line 39, column 11:
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 current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 40, column 53:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
; return returning and or

-----------------------------------------------------------------------------------------------------This is my Script----------------------------------------
declare
temp VARCHAR(20);
cursor EA_LA_1 is SELECT
   e.id Loid,
   z.id OT_Id,
   f.id Delivery_Id,
   b.id Module_Id,
   a.id Instance_Id,
   d.url Url,
   e.ENDDATE LCMS_Discontinued_Date,
   a.CREATED_BY EMS_Created_By,
   a.AVAIL_FROM EMS_Start_Date,
   a.UPDATED_BY EMS_Modified_By,  
   a.disc_from EMS_Discontinued_Date
FROM
   tpt_ext_courses_temp z,
   tpt_ext_product_catalog_temp a,
   tpt_course_content_temp b,
   fgt_not_docs_header_temp c,
   fgt_not_docs_temp d,
   lcms_data_load_temp e,
   tpt_ext_equip_cat_temp f,
   tpt_ext_delivery_mode_temp g
WHERE z.id=a.offering_temp_id
   AND a.id=b.OWNER_ID
   AND b.id=c.OWNER_ID
   AND c.id=d.OWNER_ID
   AND d.url=e.url
   AND f.id=g.delivery_id
   AND ((f.id like 'eqcat000000000000005') or (f.id like 'eqcat000000000000007'))
   AND g.offer_temp_id=z.id
   AND a.vendor_id IS NULL
   AND d.url IS NOT NULL
   AND b.custom0 IS NULL
   AND (a.disc_from IS NULL OR a.disc_from > SYSDATE)
   AND (z.disc_from IS NULL OR z.disc_from > SYSDATE) --EMS ACTIVE
   AND (e.status IN ('Notified','Launched') AND  e.startdate <= SYSDATE  AND e.enddate >= SYSDATE) --LCMS ACTIVE
   AND
            ((SELECT count(1) Cnt_OT FROM tpt_ext_product_catalog_temp y
            WHERE a.offering_temp_id=y.offering_temp_id)=1)
   AND
            ((SELECT count(1) Cnt_URL FROM tpt_course_content_temp h,
             fgt_not_docs_header_temp i, fgt_not_docs_temp j
             WHERE a.id=h.OWNER_ID AND h.id=i.OWNER_ID  AND i.id = j.owner_id
             AND h.custom0 IS NULL AND j.url IS NOT NULL)=1);

EA_LA_1_Rec  EA_LA_1%rowtype;          

Begin
   select ONE_TIME_SYNC_EMS_LCMS_SEQ.nextval into temp from dual; temp:=concat('emsrf',temp);
   
     for EA_LA_1_Rec in EA_LA_1 loop
          dbms_output.put_line(temp||EA_LA_1_Rec.Loid);
     end loop;

End;

Thanks in advance,
Pradeep Rayapudi.


0
Pradeep_Rayapudi
Asked:
Pradeep_Rayapudi
  • 2
1 Solution
 
Helena Markováprogrammer-analystCommented:
I think that problem is here - in WHERE clause there must be <<column>>=something or <<column>> IN something, there:

AND column1 IN
           ((SELECT count(1) Cnt_OT FROM tpt_ext_product_catalog_temp y
           WHERE a.offering_temp_id=y.offering_temp_id)=1)
AND column2 IN
           ((SELECT count(1) Cnt_URL FROM tpt_course_content_temp h,
            fgt_not_docs_header_temp i, fgt_not_docs_temp j
            WHERE a.id=h.OWNER_ID AND h.id=i.OWNER_ID  AND i.id = j.owner_id
            AND h.custom0 IS NULL AND j.url IS NOT NULL)=1);

I hope this will help you.

Henka
0
 
Pradeep_RayapudiAuthor Commented:
Hi Henka,

Subquery in And condition
left hand side value
(SELECT count(1) Cnt_OT FROM tpt_ext_product_catalog_temp y WHERE a.offering_temp_id=y.offering_temp_id)
that is equal's to (Right hand side value)
1.

AND
((SELECT count(1) Cnt_OT FROM tpt_ext_product_catalog_temp y WHERE a.offering_temp_id=y.offering_temp_id)=1)

Can you pls help me?What shall i do?
My email id is prayapudi@yahoo.com.

Thanks In Advance.
Pradeep Rayapudi.


0
 
Pradeep_RayapudiAuthor Commented:
Hi Henka,

   This is Pradeep again. In my WHERE clause there must be <<column>>=something is there

AND
<<column>><<this Right Hand Side>>
(SELECT count(1) Cnt_OT FROM tpt_ext_product_catalog_temp y WHERE a.offering_temp_id=y.offering_temp_id)
=
1<<Right Hand Side Value>>

Can You Please help me?

Thanks,
Pradeep.


0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now