Group By in Dynamic SQL

[skoskos]

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

[jmodic]

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)?

[skoskos]

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...

[DaPi]

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:' 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?

[skoskos]

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

The error message is : ORA-00932...

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

[DaPi]

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: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;
/

[skoskos]

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

Simon

[jmodic]

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

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?

[skoskos]

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: <the greek equivalent of the original message...>

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

Simon

[DaPi]

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?

[skoskos]

You are right ,
I make the necessary modification , but the same error message insists..

It is very peculiar but the error message
ORA-00932: inconsistent datatypes , expected -, received -

This kind of error maybe misleading ... the cause of the problem may be elsewhere...
In this statement (SQL_STMT) a series of bind variables should be used ...maybe?

Simon

[jmodic]

Have you try to concatenate all those function arguments into your SQL_STMT string? I've done that for you and that's what it comes out (words in lowercase are values from function arfuments):

SELECT count(ename),deptno FROM emp
WHERE ename=10 GROUP BY deptno;

Do you notice something strange? Something that would cause Oracle to raise ORA-00932 inconsistent datatypes error? I'd say that ename is a string, while 10 is a number. Clearly an inconsistency of datatypes.

But that's not your only problem. This dynamic SQL of yours will never work even if you correct the above error. Your SQL returns two values in your column list (COUNT and deptno), but you are fetching those two values in only one variable (CURREN_EXET_NUMBER)! Maybe you'll easier spot the error in your code if we change that dynamic SQL into a static one:

DECLARE
CURRENT_EXET_NUMBER NUMBER;
BEGIN
SELECT COUNT(ENAME), DEPTNO FROM emp
INTO CURRENT_EXET_NUMBER
WHERE DEPTNO=10 GROUP BY deptno;
END;


Can you see it now? You are fetching the value of your COUNT() into CURRENT_EXET_NUMBER, however you are fetching the value of the deptno into thin air - you have to provide another variable into which you'll fetch your deptno!

[DaPi]

Your SQL returns two values in your column list (COUNT and deptno), but you are fetching those two values in only one variable (CURREN_EXET_NUMBER)! I missed that - but the EXECUTE IMMEDIATE does manage to work! (As in my example on page 1).

P.S. It's Select ... Into ... From (it's not everyday you can catch Jurij :D )

[gamyers]

DECLARE
CURRENT_EXET_NUMBER NUMBER;
BEGIN
SELECT COUNT(ENAME), DEPTNO FROM emp
INTO CURRENT_EXET_NUMBER
WHERE DEPTNO=10 GROUP BY deptno;
END;



Firstly, you can group by a column you don't select, so this would be valid :

DECLARE
CURRENT_EXET_NUMBER NUMBER;
BEGIN
SELECT COUNT(ENAME) FROM emp
INTO CURRENT_EXET_NUMBER
WHERE DEPTNO=10 GROUP BY deptno;
END;

However since you are actually defining a single deptno in the where clause, you don't need to group by it anyway.

[skoskos]

Thanks , a lot!!!!
The main and only problem was that in the statement i select 2 columns and i try to put them into 1 variable....
Adding a local variable ... solves the dynamic function.....

Thanks , again all!!!!!

Simon