CASE expressions in FBI (SQL Window bug?)

[D.]

This looks like a possible bug:-

I'm trying to create a Function Based Index (FBI) using a CASE expression. Works fine in SQL*Plus and the Command Window, but will throw an "ORA-00900: invalid SQL statement" when run through the SQL Window. Normal FBIs can be created in the SQL Window, it just seems to be when the CASE expression is used.

Code example for replication of problem:-

CREATE TABLE t(x NUMBER);

OK now try this in a SQL Window :-

CREATE INDEX idx_f ON t(DECODE(x,1,1,NULL));

Works fine!

Now try this one :-

CREATE INDEX idx_f2 ON t(CASE WHEN x=1 THEN 1 ELSE NULL END)

ORA-00900: invalid SQL statement

Now in Command window :-

SQL> CREATE INDEX idx_f2 ON t(CASE WHEN x=1 THEN 1 ELSE NULL END);

Index created

SQL>

Works fine!

Any ideas what's wrong ?

Best regards,
D.

----------------------------

PL/SQL Developer
Version 6.0.4.906 (MBCS)
Windows 2000 version 5.0 (build 2195) Service Pack 4

Physical memory : 1,039,856 kB (620,916 available)
Paging file : 1,322,040 kB (964,968 available)
Virtual memory : 2,097,024 kB (1,983,532 available)

Using
Home: OraHome92
DLL: c:\oracle92\bin\oci.dll
OCI: version 9.2
Oracle8i Enterprise Edition Release 8.1.7.4.0
Character size: 1 byte(s)

 

[Marco Kalter]

Seems to be a parsing bug caused by the "AutoSelect Statement" SQL Window preference. If you explicitly select the statement, it works okay.

We'll fix it.