+ Reply to Thread
Results 1 to 6 of 6

Use DECODE result in WHERE clause?

  1. Use DECODE result in WHERE clause?

    I have a horrible SQL statement to write!

    I have to use a RIGHT OUTER JOIN to create a result set from 2 tables.
    I'm using DECODE to select the correct value if one side doesn't exist
    e.g -

    DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) as status

    the problem is I want to use the "status" value in the WHERE condition
    e.g

    WHERE status = "visible"

    but ORACLE always want a table field, so I have to use either
    "r.cd_status" or "p.cd_status" not a generated value!

    Is it possible to use this value? - if not is there another function/
    way of doing it?

    thanks

    harry


  2. Re: Use DECODE result in WHERE clause?

    harryajh wrote:
    > I have a horrible SQL statement to write!
    >
    > I have to use a RIGHT OUTER JOIN to create a result set from 2 tables.
    > I'm using DECODE to select the correct value if one side doesn't exist
    > e.g -
    >
    > DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) as status
    >
    > the problem is I want to use the "status" value in the WHERE condition
    > e.g
    >
    > WHERE status = "visible"
    >
    > but ORACLE always want a table field, so I have to use either
    > "r.cd_status" or "p.cd_status" not a generated value!
    >
    > Is it possible to use this value? - if not is there another function/
    > way of doing it?

    You will need to repeat the DECODE function in the WHERE clause.
    The reason why you cannot refer to an expression in the SELECT list in a
    WHERE clause is because WHERE semantically precedes SELECT.
    Think of:
    SELECT 1/c1 AS X FROM T WHERE c1 IS NOT NULL

    You don't want to get X before the WHERE clause has done it's job...

    Cheers
    Serge
    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

  3. Re: Use DECODE result in WHERE clause?

    Serge Rielau wrote:
    > SELECT 1/c1 AS X FROM T WHERE c1 IS NOT NULL

    Correction:
    SELECT 1/c1 AS X FROM T WHERE c1 <> 0

    Still early in the morning...
    Cheers
    Serge
    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

  4. Re: Use DECODE result in WHERE clause?

    On 19 Sep, 15:43, Serge Rielau wrote:
    > harryajh wrote:
    > > I have a horrible SQL statement to write!

    >
    > > I have to use a RIGHT OUTER JOIN to create a result set from 2 tables.
    > > I'm using DECODE to select the correct value if one side doesn't exist
    > > e.g -

    >
    > > DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) as status

    >
    > > the problem is I want to use the "status" value in the WHERE condition
    > > e.g

    >
    > > WHERE status = "visible"

    >
    > > but ORACLE always want a table field, so I have to use either
    > > "r.cd_status" or "p.cd_status" not a generated value!

    >
    > > Is it possible to use this value? - if not is there another function/
    > > way of doing it?

    >
    > You will need to repeat the DECODE function in the WHERE clause.
    > The reason why you cannot refer to an expression in the SELECT list in a
    > WHERE clause is because WHERE semantically precedes SELECT.
    > Think of:
    > SELECT 1/c1 AS X FROM T WHERE c1 IS NOT NULL
    >
    > You don't want to get X before the WHERE clause has done it's job...
    >
    > Cheers
    > Serge
    > --
    > Serge Rielau
    > DB2 Solutions Development
    > IBM Toronto Lab


    thanks for such a quick answer, I tried this just before I came back
    to post my findings - works fine!

    many thanks


  5. Re: Use DECODE result in WHERE clause?

    On Sep 19, 9:22 am, harryajh wrote:
    > I have a horrible SQL statement to write!
    >
    > I have to use a RIGHT OUTER JOIN to create a result set from 2 tables.
    > I'm using DECODE to select the correct value if one side doesn't exist
    > e.g -
    >
    > DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) as status
    >
    > the problem is I want to use the "status" value in the WHERE condition
    > e.g
    >
    > WHERE status = "visible"
    >
    > but ORACLE always want a table field, so I have to use either
    > "r.cd_status" or "p.cd_status" not a generated value!
    >
    > Is it possible to use this value? - if not is there another function/
    > way of doing it?
    >
    > thanks
    >
    > harry


    Use:

    WHERE DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) = 'visible';

    Or wrap the entire query in ()'s and query from the result set, at
    which time you can use your WHERE status = 'visible' syntax:

    select x.*
    from
    (select ...,..., DECODE(r.cd_status, NULL, p.cd_status, r.cd_status)
    status, ...) x
    where x.status = 'visible';


    David Fitzjarrell


  6. Re: Use DECODE result in WHERE clause?

    I think that
    DECODE(r.cd_status, NULL, p.cd_status, r.cd_status)
    can be replaced with
    NVL(r.cd_status, p.cd_status).


+ Reply to Thread