Hi guys,

I have this remote view I'm trying to select from through a database link using this simple SQL:

SELECT * FROM EMP@OTDB;

Each time I do I get the following error:

SQL> select * from emp@OTDB;
select * from emp@OTDB
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
ORA-02063: preceding line from OTDB

Strangely enough, when I run the query locally on OTDB, it works without any problems

One of the columns on this view is generated using a scalar subquery. I found that if I remove that field and recompiled, the view can be queried locally and remotely without problems.

I have searched the knowledge base, the NET and documentation unsuccessfully so far.

Both local and remote instances are running 9.2.0.4.0.


Any help is greatly apreciated.

Thanks!

A.


The code for the view is:

CREATE OR REPLACE VIEW EMP(ID_EMPLOYE,
ID_SCENAR,
ONDATE,
FROMDATE)
AS
WITH ALL_MODIF_EMPLOYE AS(SELECT EMPID,
SCENARIOID,
ONDATE
FROM EMPLOYEE_ATTRIBUTE
UNION
SELECT EMPID,
SCENARIOID,
ONDATE
FROM EMPLOYEE_SCHEDGROUPS
UNION
SELECT EMPID,
SCENARIOID,
ONDATE
FROM SL5_EMPLOYEE_ABILITIES)
SELECT TME.EMPID,
TME.SCENARIOID,
TME.ONDATE,
NVL((SELECT MIN(TME2.ONDATE) -1
FROM ALL_MODIF_EMPLOYE TME2
WHERE TME2.EMPID = TME.EMPID
AND TME2.SCENARIOID = TME.SCENARIOID
AND TME2.ONDATE > TME.ONDATE), TO_DATE('9999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
FROM ALL_MODIF_EMPLOYE TME;