Conditional return of a field in a select list


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Conditional return of a field in a select list

  1. #1
    Craig Burkett Guest

    Conditional return of a field in a select list

    I am needing to modify an Oracle SQL statement to allow for one of two table
    fields to be returned in the slect list. For instance here's a sample SQL
    psuedo statement representing what I'd like to do in PL/SQL

    SELECT IIF(FIELD1=Null OR FIELD1<>Value,FIELD2,FIELD1), FIELD3, FIELD4,
    FIELD5
    FROM TABLE1
    WHERE FIELD1=Value OR FIELD2=Value

    I want FIELD1 returned or FIELD2 returned depending on the condition being
    met (return FIELD1 if FIELD1=Value and not null; return FIELD2 if FIELD1
    doesn't match the previous criteria.

    TIA for your help

    Craig Burkett


    Share on Google+

  2. #2
    Larry Miller Guest

    Re: Conditional return of a field in a select list

    Craig,
    Try the following:

    select decode(nvl(field1,replacement_value),replacement_value,FIELD2, Value,
    FIELD2, FIELD1), FIELD3, FIELD4, FIELD5
    from table1
    where FIELD1 = Value or Field2 = Value

    In 8.1.7, the DECODE() function acts very much like the IIF() function.

    HTH.

    --
    Larry Miller
    MCSD, Microsoft MVP Visual FoxPro
    Bifrost Solutions

    "Craig Burkett" <cburkett@triadd.rr.com> wrote in message
    news:3ec91e30$1@tnews.web.devx.com...
    > I am needing to modify an Oracle SQL statement to allow for one of two

    table
    > fields to be returned in the slect list. For instance here's a sample SQL
    > psuedo statement representing what I'd like to do in PL/SQL
    >
    > SELECT IIF(FIELD1=Null OR FIELD1<>Value,FIELD2,FIELD1), FIELD3,

    FIELD4,
    > FIELD5
    > FROM TABLE1
    > WHERE FIELD1=Value OR FIELD2=Value
    >
    > I want FIELD1 returned or FIELD2 returned depending on the condition being
    > met (return FIELD1 if FIELD1=Value and not null; return FIELD2 if FIELD1
    > doesn't match the previous criteria.
    >
    > TIA for your help
    >
    > Craig Burkett
    >
    >



    Share on Google+

  3. #3
    magesh Guest

    Re: Conditional return of a field in a select list


    "Craig Burkett" <cburkett@triadd.rr.com> wrote:
    >I am needing to modify an Oracle SQL statement to allow for one of two table
    >fields to be returned in the slect list. For instance here's a sample SQL
    >psuedo statement representing what I'd like to do in PL/SQL
    >
    > SELECT IIF(FIELD1=Null OR FIELD1<>Value,FIELD2,FIELD1), FIELD3, FIELD4,
    >FIELD5
    > FROM TABLE1
    > WHERE FIELD1=Value OR FIELD2=Value
    >
    >I want FIELD1 returned or FIELD2 returned depending on the condition being
    >met (return FIELD1 if FIELD1=Value and not null; return FIELD2 if FIELD1
    >doesn't match the previous criteria.
    >
    >TIA for your help
    >
    >Craig Burkett
    >
    >


    SELECT
    case
    WHEN (FIELD1 is not null and FIELD1 =your value)
    THEN
    FIELD1
    ELSE
    FIELD2
    END nyfield,
    FIELD3, FIELD4,FIELD5
    from TABLE1

    hope that this helps you

    WHERE your clause

    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center