Group By in Dynamic SQL

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

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

Code:

---

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:

Code:

---

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!

Quote:

---

Originally Posted by jmodic

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 )

Quote:

---

Originally Posted by jmodic

Code:

---

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 :

Code:

---

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.

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