Group By in Dynamic SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Group By in Dynamic SQL

  1. #1
    Join Date
    Nov 2005
    Posts
    15

    Group By in Dynamic SQL

    Hi , everybody...

    I want somebody to write for me the general format in PL/SQL using EXECUTE IMMEDIATE clause with a group by...

    For example if a routine accepts all the components of a typical SQL clause (with aggregation , column) dynamically , how can I write this clause....
    How for instance , could we write the
    'select count(ename),deptno from emp where deptno=10 group by deptno ' using the execute immediate?

    Thanks...
    Simon

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why would GROUP BY part of the SELECT statement cause any special isues with dynamic SQL? Use GROUP BY in EXECUTE IMMEDIATE exactyl as you use it in regular SELECT statement.

    Why would GROUP BY deserve any different treatement in EXECUTE IMMEDIATE as other regular clauses of the SELECT statement do (like WHERE or ORDER BY clauses)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2005
    Posts
    15

    Reply

    Thanks,
    The problem is that when I want to interpret dynamically the statement 'select count(ename),deptno from emp where deptno=10 group by deptno '
    I write the following function

    CREATE OR REPLACE FUNCTION FNC_2WCOLUMN_1AGGRCOLUMN(TABLE_NAME_VAR IN VARCHAR2,COLUMN_NAME_VAR IN VARCHAR2,
    AGGREGATION_TYPE_VAR IN VARCHAR2,AGGR_COLUMN_VAR IN VARCHAR2)
    RETURN NUMBER

    AS
    CURRENT_EXET_NUMBER NUMBER;
    SQL_STMT VARCHAR2(500);

    BEGIN
    CURRENT_EXET_NUMBER:=0;

    BEGIN
    SQL_STMT:='SELECT '||AGGREGATION_TYPE_VAR||'('||COLUMN_NAME_VAR||'),'||AGGR_COLUMN_VAR||' FROM '||TABLE_NAME_VAR||
    ' WHERE '||COLUMN_NAME_VAR||'=10 GROUP BY '||':PAR1';
    EXECUTE IMMEDIATE SQL_STMT INTO CURRENT_EXET_NUMBER USING AGGR_COLUMN_VAR;

    END;
    RETURN CURRENT_EXET_NUMBER;
    END;
    /
    When I execute this using
    SQL>SELECT FNC_2WCOLUMN_1AGGRCOLUMN('EMP','ENAME','COUNT','DEPTNO') FROM DUAL;
    The error is :'ORA-00979 : not a goup by expression'

    What may be the problem...? I also try out some other versions of this but the error is ORA-00932...

    Thanks , again...

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    SQL_STMT:='SELECT '||AGGREGATION_TYPE_VAR||'('||COLUMN_NAME_VAR||'),'||AGGR_COLUMN_VAR||' FROM '||TABLE_NAME_VAR||
    ' WHERE '||COLUMN_NAME_VAR||'=10 GROUP BY '||':PAR1';
    EXECUTE IMMEDIATE SQL_STMT INTO CURRENT_EXET_NUMBER USING AGGR_COLUMN_VAR;
    You can't use a bind variable there! I think those last two lines would need to be:
    Code:
    ' WHERE '||COLUMN_NAME_VAR||'=10 GROUP BY '||AGGR_COLUMN_VAR;
    EXECUTE IMMEDIATE SQL_STMT INTO CURRENT_EXET_NUMBER;
    BTW what happens when it returns more than one row?
    Last edited by DaPi; 04-10-2006 at 09:43 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Nov 2005
    Posts
    15

    Reply

    Thanks ,
    but I have tried that out!!!!

    The error message is : ORA-00932...

    I cannot find , anyway , what this error implies...

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You need to check the logic of your code - I'm not sure I really want to know what you're trying to do
    This works:
    Code:
    Declare
      TABLE_NAME_VAR  VARCHAR2(20) := 'EMP';
      COLUMN_NAME_VAR  VARCHAR2(20) := 'ENAME';
      AGGREGATION_TYPE_VAR  VARCHAR2(20) := 'COUNT';
      AGGR_COLUMN_VAR  VARCHAR2(20) := 'DEPTNO';
    
      CURRENT_EXET_NUMBER NUMBER;
      SQL_STMT VARCHAR2(500);
    
    BEGIN
    CURRENT_EXET_NUMBER:=0;
    
    BEGIN
    SQL_STMT:='SELECT '||AGGREGATION_TYPE_VAR||'('||COLUMN_NAME_VAR||'),'||AGGR_COLUMN_VAR||' FROM '||TABLE_NAME_VAR||
    ' WHERE '||AGGR_COLUMN_VAR||'=10 GROUP BY '||AGGR_COLUMN_VAR;
    EXECUTE IMMEDIATE SQL_STMT INTO CURRENT_EXET_NUMBER;
    
    END;
    dbms_output.put_line(to_char(CURRENT_EXET_NUMBER));
    END;
    /
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Nov 2005
    Posts
    15

    Reply

    Unfortunately and peculiar indeed but the error message ORA-00932 appears.

    Simon

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by skoskos
    The error message is : ORA-00932...

    I cannot find , anyway , what this error implies...
    It implies that you are mixing two incompatible datatypes:

    ORA-00932 inconsistent datatypes

    Quote Originally Posted by skoskos
    Thanks ,
    but I have tried that out!!!!
    You have tried WHAT? You've only showed us what you've tried in the first place, when you've totaly erroneously used bind variables in places where they can't be used. Can you please show us (copy+paste of the actual code and the result together with the complete error messages would do perfectly) what and how you've tried to implement what DaPi has suggested?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2005
    Posts
    15

    Reply

    Thanks , for your inetrest , indeed....
    The actual code is as follows...

    CREATE OR REPLACE FUNCTION FNC_2WCOLUMN_1AGGRCOLUMN(TABLE_NAME_VAR IN VARCHAR2,COLUMN_NAME_VAR IN VARCHAR2,
    AGGREGATION_TYPE_VAR IN VARCHAR2,AGGR_COLUMN_VAR IN VARCHAR2)
    RETURN NUMBER

    AS
    CURRENT_EXET_NUMBER NUMBER;
    SQL_STMT VARCHAR2(500);

    BEGIN
    CURRENT_EXET_NUMBER:=0;

    BEGIN
    SQL_STMT:='SELECT '||AGGREGATION_TYPE_VAR||'('||COLUMN_NAME_VAR||'),'||AGGR_COLUMN_VAR||' FROM '||TABLE_NAME_VAR||
    ' WHERE '||COLUMN_NAME_VAR||'=10 GROUP BY '||AGGR_COLUMN_VAR;
    EXECUTE IMMEDIATE SQL_STMT INTO CURRENT_EXET_NUMBER;

    END;
    RETURN CURRENT_EXET_NUMBER;
    END;

    When I call the above function from SQL , I get ...

    SQL> select FNC_2WCOLUMN_1AGGRCOLUMN('EMP','ENAME','COUNT','DEPTNO') FROM EMP;
    -------
    select FNC_2WCOLUMN_1AGGRCOLUMN('EMP','ENAME','COUNT','DEPTNO') FROM EMP

    ORA-00932:

    I have also tried the anonymous block - proposed by DAPI- with the same results....

    Simon

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Did you notice that I changed this:
    ' WHERE '||COLUMN_NAME_VAR||'=10
    to something else, did you notice I put it in BOLD and have you thought why I might have done that?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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



Click Here to Expand Forum to Full Width