Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Lightbulb Unanswered: Using selects as an expression inside the decode function

    Hi,
    I'm new at oracle sql and i have a big doubt:

    Is it possible to use selects as an expression inside the decode function?

    What I want is something like this:
    ==================================
    select
    ttfacr200100.t$ttyp,
    [...],
    decode(
    ttipcs020100.t$kopr,
    2,select ttidmt031100.t$rspcb
    from ttidmt031100
    where ttidmt031100.t$cprj
    in (select ttipcs026100.t$mprj
    from ttipcs026100
    where ttipcs026100.t$sprj=ttipcs020100.t$cprj)
    ,3, select ttidmt031100.t$rspcb from ttidmt031100
    where ttidmt031100.t$cprj = ttipcs020100.t$cprj
    ,0
    ) rspcb
    from
    ttfacr200100,
    [...],
    ttccom001100

    where
    ttfacr200100.t$proj = ttipcs020100.t$cprj and
    [...] and
    ttfacr200100.t$balh > 0
    =====================================
    The (full) query is returning an "missing expression" error in the expressions of the decode.


    Help would be appreciated.

    Regards,
    Miguel

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    Yes you can - just add some parentheses around the selects.

    Here is a (very silly) example:

    SQL> select ename,
    2 decode( deptno, 10, (select dname from dept where deptno=10), (select 'x' from dual) )
    3* from emp;

    ENAME DECODE(DEPTNO,
    ---------- --------------
    SMITH x
    ALLEN x
    WARD x
    JONES x
    MARTIN x
    BLAKE x
    CLARK ACCOUNTING
    SCOTT x
    KING ACCOUNTING
    TURNER x
    ADAMS x

    ENAME DECODE(DEPTNO,
    ---------- --------------
    JAMES x
    FORD x
    MILLER ACCOUNTING

    14 rows selected.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    BTW, what sadist came up with table names like ttipcs020100 and column names like t$kopr?

  4. #4
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Re: Using selects as an expression inside the decode function

    Hi,
    Thanks for the prompt reply.
    I tried to run your query but i get the same error

    Any suggestions?

    ==========================================
    1 select ename,
    2 decode( deptno, 10, (select dname from dept where deptno=10), (select 'x' from dual) )
    3* from emp
    decode( deptno, 10, (select dname from dept where deptno=10), (select 'x' from dual) )
    *
    ERRO na linha 2:
    ORA-00936: missing expression

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    What Oracle version are you on? I ran that SQL successfully on Oracle8i Enterprise Edition Release 8.1.7.3.0

  6. #6
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Talking Re: Using selects as an expression inside the decode function

    Originally posted by andrewst
    BTW, what sadist came up with table names like ttipcs020100 and column names like t$kopr?
    Actually, these are BaaN table and column names. I can explain the meaning of it if you wish.

    Regards,
    Miguel

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    Originally posted by msantos
    Actually, these are BaaN table and column names. I can explain the meaning of it if you wish.

    Regards,
    Miguel
    No thanks! I'll just remember to avoid ever working with BaaN

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Query

    Hi
    I am not sure how BaaN would probably work, but you might wanna try giving the result of the decode a name or something
    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Question Re: Using selects as an expression inside the decode function

    Originally posted by andrewst
    What Oracle version are you on? I ran that SQL successfully on Oracle8i Enterprise Edition Release 8.1.7.3.0

    Should this be a problem?

    SQL> select * from product_component_version
    2 ;

    [...]
    PRODUCT
    ----------------------------------------------------------------
    VERSION
    ----------------------------------------------------------------
    STATUS
    ----------------------------------------------------------------

    Oracle8 Enterprise Edition
    8.0.6.1.0
    Production

    PL/SQL
    8.0.6.1.0

    [...]

  10. #10
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Re: Query

    Originally posted by aruneeshsalhotr
    Hi
    I am not sure how BaaN would probably work, but you might wanna try giving the result of the decode a name or something
    Thanx and Regards
    Aruneesh
    I was trying to build this query to use it in Crystal Reports,
    but thanks anyway.

  11. #11
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Versions (TNS)

    Hi
    As we started discussing about the versions of Oracle, I did the above mentioned command from msantosh, and got

    PRODUCT VERSION STATUS
    ---------------------------- --------- -----------
    NLSRTL 3.4.1.0.0 Production
    Oracle8i Enterprise Edition 8.1.7.0.1 Production
    PL/SQL 8.1.7.0.0 Production
    TNS for Linux: 8.1.7.0.0 Development

    I wanted to know the purpose of
    1. NLSRTL
    2. TNS for Linux

    Thanx and Regards
    Aruneesh

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    Originally posted by msantos
    Should this be a problem?

    Oracle8 Enterprise Edition
    8.0.6.1.0
    Production
    [...]
    Almost certainly. That's a very old version of Oracle you have there (even older than the one I have here!) Scalar subqueries are a relatively recent feature, so I guess they were introduced in 8.1. In which case, you will have to change your approach.

  13. #13
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Unhappy Re: Using selects as an expression inside the decode function

    Originally posted by andrewst
    Almost certainly. That's a very old version of Oracle you have there (even older than the one I have here!) Scalar subqueries are a relatively recent feature, so I guess they were introduced in 8.1. In which case, you will have to change your approach.
    Great. Any suggestions? I'm really newbie in Oracle.

    Thanks again for the promptness.

    Regards,
    Miguel

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Versions (TNS)

    Originally posted by aruneeshsalhotr
    Hi
    As we started discussing about the versions of Oracle, I did the above mentioned command from msantosh, and got

    PRODUCT VERSION STATUS
    ---------------------------- --------- -----------
    NLSRTL 3.4.1.0.0 Production
    Oracle8i Enterprise Edition 8.1.7.0.1 Production
    PL/SQL 8.1.7.0.0 Production
    TNS for Linux: 8.1.7.0.0 Development

    I wanted to know the purpose of
    1. NLSRTL
    2. TNS for Linux

    Thanx and Regards
    Aruneesh
    1. NLSRTL = National Language Support Runtime Library

    This deals with language-specific date formats, error messages etc.

    2. TNS = Transparent Network Substrate

    This deals with communication between client and server. Presumably you have Oracle installed on a Linux server.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using selects as an expression inside the decode function

    Originally posted by msantos
    Great. Any suggestions? I'm really newbie in Oracle.

    Thanks again for the promptness.

    Regards,
    Miguel
    One possibility is to divide the query into a number of separate queries, one for each DECODE condition, and then UNION the results together.

    e.g. instead of:

    select ename,
    decode( deptno,
    10, (select dname from dept where dept.deptno = emp.deptno),
    20, (select loc from dept where dept.deptno = emp.deptno),
    , 'x' )
    from emp;

    do this:

    select ename, dname
    from emp, dept
    where emp.deptno = dept.deptno
    and emp.deptno = 10
    UNION ALL
    select ename, loc
    from emp, dept
    where emp.deptno = dept.deptno
    and emp.deptno = 20
    UNION ALL
    select ename, 'x'
    from emp
    where emp.deptno not in (10,20);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •