The following query results in an error, "ORA-00907 missing right parenthesis" at the beginning of the word "fieldtocase"....
I've tried many variants of this with no success.
SELECT field1, field2, field3, (CASE fieldtocase WHEN 'a' THEN 'A it is' WHEN 'b' THEN 'B is to be' END) casedfield FROM table1;
I'm using Oracle8i Enterprise Edition Release 22.214.171.124.0 - Production.
That error is related to "ORA-00923: FROM keyword not found where expected." (In fact, if you remove the parentheses, which are not required, that's exactly what you'll get.) Both messages really mean, "The parser is completely lost." When the parser got to the point indicated, it did not know what to make of the next item.
In your example, the parser is confused because the next word after "CASE" (in Oracle 8) must be "WHEN": Simple CASE expressions, like yours, work in Oracle 9; but in version 8 your best options are DECODE or a searched CASE expression. See my earlier question on CASE to see a statement very much like yours written all three ways.