Conditional return of a field in a select list

[Craig Burkett]

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

[Larry Miller]

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
>
>

[magesh]

"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