Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: Decode Statement Within Where Clause?

    Is it possible to do a Decode statement within a Where clause? I'm trying to write SQL that will pull the Branch code from 2 different tables. If it's Null within the Trx table then pull from the Sales table.
    I tried doing an Or clause such as below, but it pulls double if it's in both tables for 1 request.

    AND (TRXINFO.FIN_TRX_BRANCH_ID = MB.MANDI_BRANCH_ID
    OR FRI.REQ_SALES_OFFICE_CODE = MB.MANDI_BRANCH_ID)

    Here is my so called Decode statement within a where clause.

    AND DECODE(TRXINFO.FIN_TRX_BRANCH_ID, NULL, FRI.REQ_SALES_OFFICE_CODE = MB.MANDI_BRANCH_ID,
    TRXINFO.FIN_TRX_BRANCH_ID = MB.MANDI_BRANCH_ID)

    Can someone help with this concept or give me ideas?
    Thanks.

    Carla

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd be happier if you posted some more relevant information (for example, Oracle database version, CREATE TABLE and INSERT INTO sample data statements) so that it would be easier to do it for you.

    Now I tried to create a *working* query which might server as an example for you - see if it suits your needs and, if so, try to implement it into your case. The general idea is what you wanted - the only difference is using the CASE instead of DECODE.
    Code:
    SQL> select ename
      2  from emp e
      3  where e.deptno = case when mgr is null then
      4                          (select deptno from dept where loc = 'BOSTON')
      5                        else
      6                          (select deptno from dept where loc = 'NEW YORK')
      7                   end;
    
    ENAME
    ----------
    CLARK
    MILLER
    
    SQL>

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    You could use NVL(), or you could convert it into 2 queries using a UNION ALL format.

    --=cf

  4. #4
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    function nvl2 can help you.
    http://download-east.oracle.com/docs...htm#sthref1727

    but here is potential problem. good time for data model review.
    (when this will frequently used select)

    follow up next link: (find out "conditional")
    http://asktom.oracle.com/pls/asktom/...:1460004570029
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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