views:

1728

answers:

5

I have a simple function written in Oracle 9i (version 9.2.0.4.0) to simulate an in-line IF. For those interested, here's the code:

create or replace
FUNCTION IIF  
   (testExpression NUMBER,
    trueResult NUMBER,
    falseResult NUMBER)
RETURN NUMBER   
AS
BEGIN
   /*
      A simple in-line IF function for use with SQL queries. If the test
      expression evaluates to any non-zero value, it is considered to be 
      true, and the trueResult is returned. Otherwise, falseResult is
      returned.
   */   
   IF (testExpression is null) or (testExpression = 0) THEN
      return falseResult;
   ELSE
      return trueResult;
   END IF;
END IIF;

This isn't rocket science. Now, here's the big mystery: if I execute the following SQL statements, everything is just fine, and works exactly as I expect:

SELECT IIF(1, 'true', 'false') FROM DUAL;
SELECT IIF(0, 'false', 'true') FROM DUAL;

However, the following generates a really bizarre error from Oracle:

SELECT IIF((0 = 1), 'false', 'true') FROM DUAL;

That error is as follows:

ORA-00907: missing right parenthesis.

Clearly, that isn't the case. Would anyone happen to have an explanation for this little bit of bizarreness?

It's taking a whole lot of self-control at the moment to restrain myself from hurling the Oracle server out the window. Oracle seems rife with these kinds of inanities.

EDIT: Is there some kind of magic syntax I have to use to use an equality operator in a select statement?

A: 

Having never worked with oracle I can't directly offer any sound advice, but looking at the statement it could be oracle having a problem inferring number from boolean?..

The IIF signature is expecting a NUMBER as the first argument and in the call you are passing a BOOL evaluation.

This is just a thought.

Quintin Robinson
+5  A: 

Maybe you can explain what you are trying to do. I think you are looking for the CASE or DECODE functions, but I can't be sure. It seems like you are working against the grain of the SQL language for some reason.

The error occurs because Oracle doesn't expect a relational operator in the select clause of the query:

SQL> SELECT IIF(1>7, 0, 1) FROM DUAL;
SELECT IIF(1>7, 0, 1) FROM DUAL
            *
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> SELECT 1=0 FROM DUAL;
SELECT 1=0 FROM DUAL
        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

See this Ask Tom article.

Jon Ericson
First, thanks for the pointer to the article (even though I think that Tom is arguing for the sake of arguing, and shows a *very* stubborn streak). I understand now why it's not working. (cont'd)
Mike Hofer
That said, the reason I want to do this this way is for brevity in some very long, complex SQL statements I'm refactoring, and I wanted to get them broken down for clarity. It ASTOUNDS me that you can't do something like that in Oracle. Then again, I shouldn't really be surprised.
Mike Hofer
I'm *still* not sure why this is needed. How does an IFF function help? In my experience, very long, complex SQL statements should be reformatted rather than refactored.
Jon Ericson
+1  A: 
  1. There is no BOOLEAN type in the Oracle database, so you can't use an expression like "(1=0)".
  2. Even if there were a BOOLEAN type, you have declared the first argument to be of type NUMBER. You have to pass it a number, or something that can be implicitly converted to one.
  3. Finally, I don't understand how any of your examples actually produced anything besides "invalid number", since your second and third arguments can't be converted to numbers, either.
DCookie
+3  A: 

You want

SELECT CASE WHEN expr then 'true' else 'false' end col_alias FROM DUAL;

or for simple equality test

SELECT DECODE(val1,val2,'true','false') col_alias FROM dual;

This will perform much better than dropping out to a PL/SQL function. There's a number of reasons why your approach doesn't work.

  1. Booleans aren't an Oracle data type.

  2. They are a PL/SQL datatype, but even then there's no implicit conversion to numbers (zero false, non-zero true)

  3. There's no 'expression' datatype, so the SQL engine can't pass the expression to PL/SQL.

  4. Even if you passes the expression as a string to PL/SQL, the PL/SQL engine cannot dynamically evaluate an expression in a string. It would have to build the string into a dynamic SQL statement and execute that dynamically, returning a result (in a valid SQL datatype, like a string or number).

Gary
A: 

Besides decode and case, there is also nvl.

tuinstoel