Hi,

I'm migrating our existing database on 8.1.7 to 9.2 and I keep getting an ORA-00903 error (on Oracle 9.2.0.1.0) when I try to select from a view with nested selects. Here's a very simple example - no rows are returned when I issue the query (its a dummy query, but it illustrates the problem). However, as soon as I turn it into a view, and do a select from the view, I get an Invalid Table Name error. Can anyone suggest why this is happening?

So... This works on its own

SELECT ID
FROM (
SELECT /*+ ORDERED */
t.id
FROM tblmsg t,
tblevent e,
e_workflowstate s,
(SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowplntasks ws
WHERE s.e_taskstatus_id = ws.taskid
UNION ALL
SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowmantasks ws
WHERE s.e_taskstatus_id = ws.taskid
UNION ALL
SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowtrgtasks ws
WHERE s.e_taskstatus_id = ws.taskid) V
WHERE t.id = e.id
AND t.id = v.e_taskstatus_id
AND V.e_taskstatus_id = e.id)

But this doesn't

CREATE OR REPLACE VIEW TESTVIEW AS
SELECT ID
FROM (
SELECT /*+ ORDERED */
t.id
FROM tblmsg t,
tblevent e,
e_workflowstate s,
(SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowplntasks ws
WHERE s.e_taskstatus_id = ws.taskid
UNION ALL
SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowmantasks ws
WHERE s.e_taskstatus_id = ws.taskid
UNION ALL
SELECT e_taskstatus_id
FROM e_taskstatus s,
e_workflowtrgtasks ws
WHERE s.e_taskstatus_id = ws.taskid) V
WHERE t.id = e.id
AND t.id = v.e_taskstatus_id
AND V.e_taskstatus_id = e.id);

SELECT * FROM TESTVIEW;

ERROR at line 1:
ORA-00903: invalid table name


This works fine on 8.1.7. I haven't tested it on 9.1 though.

Any ideas?

Thanks

T.