-
Error ORA-00928 when querying view through dblink
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|