-
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
-
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?
-
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...
-
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
-
Reply
Thanks ,
but I have tried that out!!!!
The error message is : ORA-00932...
I cannot find , anyway , what this error implies...
-
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
-
Reply
Unfortunately and peculiar indeed but the error message ORA-00932 appears.
Simon
-
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
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|