In-line IF statement

[Deleco]

Hi,

I would like to use an in-line IF statment my oracle SQL it does not appear to work is there any other way of doing this

IIF([FieldName]='Expert', 1, 0)

I am using the Crystal Reports Command feature this allows me to USE SQL to narrow the amount of records soming back. Apparently the SQL is bound to the driver being used, i am using the ORACLE ODBC Driver. Any help would be appeciated.

Many thanks in Advance

Deleco

 

[carp]

IF is a PL/SQL construct, not SQL.

Try DECODE:

SELECT DECODE(my_field_name,'Expert',1,0)
FROM my_table
WHERE some_condition_exists;

Alternatively, you might try using a CASE construct.

 

[Deleco]

Thanks Crystal seems to like the syntax of that.

Is there anyway you can use a like test using the DECODE statement. as in

DECODE(field_name, like 'Expert*', 1, 0)

Also which is the true and false parts in the example above would 1 be the true and 0 be the false.

Thanks for the help

Deleco

 

[SantaMufasa]

At the risk of interfering with Carp's excellent support, here is what you want to compare as you wish:

...DECODE(substr(field_name,1,6),'Expert', 1, 0)...

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:05 (13Sep04) UTC (aka "GMT" and "Zulu"), 10:05 (13Sep04) Mountain Time)

 

[carp]

Yes - 1 is if the field is like Expert and 0 is if it is not.

And Mufasa - please feel free to interfere at any time! As usual, you are right on the mark!