DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Fct decode

  1. #1
    Join Date
    Mar 2002
    Posts
    303

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  3. #3
    Join Date
    Mar 2002
    Posts
    303
    Is part of a big problem.
    Is it possibilities to make a select in a decode

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Pando


    Thas cool...:-).ben sorry i never thought this was possible ...

    regards
    Hrishy

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!!!!!

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width