how to give the select statement in a "DECODE" function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    Delhi
    Posts
    108
    Hi,
    i want to give a select statement inside a decode statement.How do i do this.

    i want something like

    select col1,col2,decode(select count(*) from tab1 where col1=20,0,'false',
    1,'true') col3
    from tab1,tab2
    where.....

    is it possible...Inside the subquery,if the select statement returns me 0,the value should be false and if 1,it should be true.
    The subquery returns either 0 or 1 only.


    Urgent
    thanks
    Sonu

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    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.
    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
    Feb 2001
    Posts
    103
    ok here's the solution

    You can write a select statment into a decode functions..

    Select e.empno,e.ename,e.sal,decode(e.deptno,10,(select
    decode(f.deptno,0,'true',1,'false',null) from emp f where e.empno=f.empno),null) from emp e.

    I am performing the check for deptno you can accordingly substitute your values and check.. I hope this will help.. In case if it does not then let me know.
    When the going gets tough, the tough gets going

  4. #4
    Join Date
    Feb 2001
    Posts
    103
    ok here's the solution

    You can write a select statment into a decode functions..

    Select e.empno,e.ename,e.sal,decode(e.deptno,10,(select
    decode(f.deptno,0,'true',1,'false',null) from emp f where e.empno=f.empno),null) from emp e.

    I am performing the check for deptno you can accordingly substitute your values and check.. I hope this will help.. In case if it does not then let me know.
    When the going gets tough, the tough gets going

  5. #5
    Join Date
    May 2000
    Location
    Delhi
    Posts
    108

    ya got the solution

    Thanks friends i got the sloution
    You can use a select statement inside decode as long as you can cast it as a expression .
    select sysdate , decode((select count(*) from dual where rownum > 0 ) ,0,'false', 1 ,'true') from dual where 2>1 ;


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