Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: How to fix the group by in ORACLE 11

    We are upgrading to ORACLE 11 and I am reviewing packages, functions, procedures, etc and I came across a procedure calling functions in a package that is not working in ORACLE 11. Does not like the DECODE in ORACLE 11.

    this will compile, but will not RUN.
    Any help would be appreciated.



    ORA-00979: not a GROUP BY expressionThe code:
    -------

    FUNCTION all_undup_by_credit_noncredit (TermCode TermCodeType) RETURN owner.table_tb_r_count PIPELINED AS

    -- Local Variables
    -- ---------------
    TheTerm TermCodeType := NULL;
    ResultRow table.my_t_r_count := table.my_t_r_count(NULL,NULL,NULL,NULL);

    -- Embeded Cursors
    -- ---------------

    CURSOR GetCounts IS
    SELECT DECODE(table.my_pk_student.f_is_credit_student(ID_ num,TheTerm),'Y','Credit','NonCrd') as credit
    , COUNT(DISTINCT ID_num as countval
    FROM table_name
    WHERE table_column_term_code = TheTerm and
    table_column_reg_code in ('RW','RE','AU')
    GROUP BY DECODE(table.my_pk_student.f_is_credit_student(ID_ num,TheTerm),'Y','Credit','NonCrd';
    -- ------------------
    BEGIN

    FOR CountSet in GetCounts
    LOOP
    ResultRow.CountCode1 := CountSet.credit;
    ResultRow.CountCode2 := NULL;
    ResultRow.CountCode3 := NULL;
    ResultRow.CountValue := CountSet.countval;

    pipe ROW(ResultRow);

    END LOOP;

    RETURN;
    END all_undup_by_credit_noncredit;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >GROUP BY DECODE(table.my_pk_student.f_is_credit_student(ID_ num,TheTerm),'Y','Credit','NonCrd';

    SQL Parser complains when mis-match parenthesis exist.
    How many "(" exist when compared to how many ")" exist above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    3

    How to fix the group by in ORACLE 11

    No just missed the ') at end when copy/paste

    GROUP BY DECODE(table.my_pk_student.f_is_credit_student(ID_ num,TheTerm),'Y','Credit','NonCrd');


    Thanks, sometimes it always helps to look at those '()' more than once.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >No just missed the ') at end when copy/paste
    Right!
    COPY & PASTE dropped the closing ")" but kept the trailing semicolon.

    It works for me.
    Code:
    SQL> select decode(deptno,20,99), sum(sal) from emp group by decode(deptno,20,99);
    
    DECODE(DEPTNO,20,99)   SUM(SAL)
    -------------------- ----------
    			  18150
    		  99	  18650
    
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE	11.2.0.1.0	Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by anacedent View Post
    >No just missed the ') at end when copy/paste
    Right!
    COPY & PASTE dropped the closing ")" but kept the trailing semicolon.
    Yes, that's how COPY & PASTE works in Oracle 11g .

    @PNW_883, could you really copy & paste your SQL*Plus session? Enclose it into [code] tags in order to improve readability.

  6. #6
    Join Date
    Oct 2011
    Posts
    3

    How to fix the group by in ORACLE 11

    I am only hoping for guidence to a solution that has recently occured since instance was upgraded to 11g.

    Same GROUP BY (DECODE... statement in 10.2.0 ORACLE with no problems or errors when procedures are run.

    Thanks for the posting tips.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    if GROUP BY DECODE is broken, then you should file Bug Report with Oracle Support
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •