-
Fct decode
Hi all,
know someone how to make the first argument for a decode a sql statment??
Example:
decode(select col1 from product,1;'TEST',.......)
Thank's for answer.
-
What's wrong with:
select decode(col1,1,'TEST','OTHER') from product
? ? ? ?
Or is this part of a bigger problem?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Is part of a big problem.
Is it possibilities to make a select in a decode
-
Hi
Nope its not possible to make a select in a decode.
you should pose your requirement and tell us what exactly you want..
regards
Hrishy
-
that´s call scalar subquery and it only works from 8i, although I am not sure if it fits your requirements
Code:
lsc@LNX920-RAC1>select decode((select dname from dept where dept.deptno = emp.deptno), 'ACCOUNTING', 10, 100) OMG
2 from emp;
OMG
----------
100
100
100
100
100
100
10
100
10
100
100
100
100
10
14 rows selected.
-
Hi Pando
Thas cool...:-).ben sorry i never thought this was possible ...
regards
Hrishy
-
Okay, okay ...
So how the hell does that work then?
I tried that way and then a traditional one:
SELECT e.empno, DECODE(d.deptname,'ACCOUNTING','100','10')
FROM emp e, dept d
WHERE d.deptno = e.deptno
And got the same result. So ... "What", I thought, "is the advantage of the 'clever Pando' method?". So I EXPLAINED both statements and found the traditional does :
QUERY_PLAN
--------------------------------------------------------------------
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP
(My emp and dept tables have no constraints or indexes!)
Whilst the clever method does:
QUERY_PLAN
--------------------------------------------------
TABLE ACCESS FULL EMP
So how the hell does it know 'ACCOUNTING' = whatever the code is in the EMP table without reading the DEPT table to check what the code is?
I'm confused but very impressed.
-
The second explain plan is telling you only the half of the story (FULL SCAN on emp, but no mention of any action of the operations on dept). That is because the operations on scalar subqueries are never shown in explain plan of the parent query. This is not the case only with this particular scalar subquery inside the decode, but with scalar subqueries in general.
So for example, the following two queries are returning identical results, yet their explain plans will be totaly different:
select e.ename, d.deptname
from emp e, dept d
where e.deptno = d.deptno;
select
ename, (select deptname from dept where deptno = e.deptno) deptname
from emp e;
The explain plan for the second query will show only full scan on emp, it won't show any operation on dept whatsoever.
And what is actual execution plan difference among the two? Well, the actual execution might be identical or it might be drasticaly different. With scalar subqueries Oracle actually has no choice but to use "nested loops approach". While on the other hand for ordinar join query it can choose from any of the available join approaches (nested loop, merge join, hash join).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
That is because the operations on scalar subqueries are never shown in explain plan of the parent query
which is a shame!!!!!
-
Hi
even tkprof doesnt show it :-)
regards
Hrishy
Last edited by hrishy; 04-24-2003 at 07:19 AM.
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
|