-
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
-
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?
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|