7 Replies Latest reply: Feb 25, 2013 10:45 PM by 11g.DBA RSS

    Missing right parenthesis

    11g.DBA
      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
          oralicious
          when did the IF statement become usable in SQL?

          use decode or case statement instead
          • 2. Re: Missing right parenthesis
            Fran
            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
              oralicious
              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
                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
                  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
                    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
                      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