how to give the select statement in a "DECODE" function

[jmodic]

You can't use select statement inside the decode expression like you tried to.

But you can make use of inline view to get what you want. Here is an example:

SQL> select empno, ename, decode(dept_cnt, 6, 'big', 5, 'medium', 'small') dept_size
  2  from
  3  (select empno, ename, (select count(*) from scott.emp where deptno = x.deptno) dept_cnt
  4  from scott.emp x);

     EMPNO ENAME      DEPT_S
---------- ---------- ------
      7369 SMITH      medium
      7499 ALLEN      big
      7521 WARD       big
      7566 JONES      medium
      7654 MARTIN     big
      7698 BLAKE      big
      7782 CLARK      small
      7788 SCOTT      medium
      7839 KING       small
      7844 TURNER     big
      7876 ADAMS      medium
      7900 JAMES      big
      7902 FORD       medium
      7934 MILLER     small

14 rows selected.