This discussion is archived
7 Replies Latest reply: Feb 25, 2013 8:45 PM by 11g.DBA RSS

Missing right parenthesis

11g.DBA Newbie
Currently Being Moderated
Hi,
select c.pvms_no,
     c.nomenclature,
     concat(c.pvms_no,c.nomenclature) AS mas_store_item_nomenclature,
     b.remarks,
     b.mmf_in_qty as reqMMF,
     a.mmf_for_the_year,
     a.mmf_store_type,
     d.department_name,
     b.curr_year_mmf as apprMMF,
     e.item_unit_name,
     b.Curr_year_mmf as current_,
     b.prev_year_mmf as previous,
     sum(if(a.mmf_for_the_year=2009,b.curr_year_mmf,0)) as prevyear,
     sum(if(a.mmf_for_the_year=2010,b.curr_year_mmf,0)) as Appr1MMF
from
     store_mmf_department_m a,
     store_mmf_department_t b,
     mas_store_item c,
     mas_department d,
     mas_store_item_conversion e
group by
     c.pvms_no,
     c.nomenclature,
     b.remarks,
     b.mmf_in_qty,
     a.mmf_for_the_year,
     a.mmf_store_type,
     d.department_name,
     b.curr_year_mmf,
     e.item_unit_name,
     b.Curr_year_mmf,
     b.prev_year_mmf

sum(if(a.mmf_for_the_year=2009,b.curr_year_mmf,0)) as prevyear,
                                     *
ERROR :
ORA-00907: missing right parenthesis
Please help me where I am going wrong.

Edited by: 11g.DBA on Feb 25, 2013 3:49 PM
  • 1. Re: Missing right parenthesis
    User477708-OC Journeyer
    Currently Being Moderated
    when did the IF statement become usable in SQL?

    use decode or case statement instead
  • 2. Re: Missing right parenthesis
    Fran Guru
    Currently Being Moderated
    IF is the cause, you are using it wrong.

    How to use it (with examples):
    http://www.techonthenet.com/oracle/loops/if_then.php
  • 3. Re: Missing right parenthesis
    User477708-OC Journeyer
    Currently Being Moderated
    Fran wrote:
    IF is the cause, you are using it wrong.

    How to use it (with examples):
    http://www.techonthenet.com/oracle/loops/if_then.php
    Fran, IF was always only a plsql command, has they translated to SQL statements now? decode and case were always the way to implement IF THEN ELSE logic in a SQL statement
  • 4. Re: Missing right parenthesis
    11g.DBA Newbie
    Currently Being Moderated
    select c.pvms_no,
            c.nomenclature,
            concat(c.pvms_no,c.nomenclature) AS mas_store_item_nomenclature,
            b.remarks,
      2    3    4    5      b.mmf_in_qty as reqMMF,
      6     a.mmf_for_the_year,
      7     a.mmf_store_type,
      8     d.department_name,
      9     b.curr_year_mmf as apprMMF,
     10     e.item_unit_name,
     11     b.Curr_year_mmf as current_,
     12     b.prev_year_mmf as previous,
     13     sum(case (when a.mmf_for_the_year=2009 then b.curr_year_mmf else 0)end) as prevyear,
     14     sum(case (when a.mmf_for_the_year=2010 then b.curr_year_mmf else 0)end) as Appr1MMF
     15  from
     16     store_mmf_department_m a,
     17     store_mmf_department_t b,
     18     mas_store_item c,
     19     mas_department d,
     20     mas_store_item_conversion e
     21  group by
     22     c.pvms_no,
     23     c.nomenclature,
     24     b.remarks,
     25     b.mmf_in_qty,
     26     a.mmf_for_the_year,
     27     a.mmf_store_type,
     28     d.department_name,
     29     b.curr_year_mmf,
     30     e.item_unit_name,
     31     b.Curr_year_mmf,
     32     b.prev_year_mmf;
            sum(case (when a.mmf_for_the_year=2009 then b.curr_year_mmf else 0)end) as prevyear,
                           *
    ERROR at line 13:
    ORA-00907: missing right parenthesis
  • 5. Re: Missing right parenthesis
    Fran Guru
    Currently Being Moderated
    How to use case:
    http://www.adp-gmbh.ch/ora/sql/case_when.html

    I don't know what are you trying to do, but i think you must use "decode" like 961469 proposed to you.
  • 6. Re: Missing right parenthesis
    JohnWatson Guru
    Currently Being Moderated
    I see that you have marked this question as answered. Are you sure it is giving the correct result? Your query does not inlude any JOIN clauses, so you are aggregating a cross join of all five tables. Are your SUMs larger than expected?
  • 7. Re: Missing right parenthesis
    11g.DBA Newbie
    Currently Being Moderated
    Hi John,
    Actually My developers need the query as it is,I already told them about the drawback of cross join but they want it that way.

    Anyway thanks for concern reply

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points