请问一个关于ora－00907的问题

Question

SELECT FileID AS "ID", FILENO, TITLE, KRISADMIN."STATUS"."DESCRIPTION" AS "STATUS", KRISADMIN."SECURITYLEVEL"."DESCRIPTION" AS "GRADING", KRISADMIN."COMPARTMENT"."NAME" AS "NAME" FROM KRISADMIN."FILE" LEFT JOIN KRISADMIN."STATUS" ON KRISADMIN."FILE".STATUSID = KRISADMIN."STATUS".STATUSID LEFT JOIN KRISADMIN."SECURITYLEVEL" ON KRISADMIN."FILE".SECURITYLEVELID = KRISADMIN."SECURITYLEVEL".SECURITYLEVELID LEFT JOIN KRISADMIN."COMPARTMENT" ON KRISADMIN."FILE".COMPARTMENTID = KRISADMIN."COMPARTMENT".COMPARTMENTID WHERE (UPPER(FileNo) LIKE 'CEO%' OR UPPER(Title) LIKE '%CEO%'OR UPPER(KRISADMIN."STATUS"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."SECURITYLEVEL"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."COMPARTMENT"."NAME") LIKE '%CEO%')AND (FILEID IN (SELECT "ChildID" FROM KRISADMIN.UDF_COMPTFILETREE('2','1'))  OR FILEID IN (SELECT "ChildID" FROM KRISADMIN.UDF_SUBJFILETREE('2','1'))  OR FILEID IN (SELECT "ChildID" FROM KRISADMIN.UDF_PUBLICCOMPTFILETREE('1')))ORDER BY "NAME",FILENO,TITLE错误代码是ora－00907，网上查是说order by的问题，但是去了order by后还是一样的错误。。错误显示始终是在IN 上。。就是FILEID IN 。只要有一个Fileid IN存在就出错。。这个语句是在MSSQL里测试是通过的。。感觉上MSSQL和ORACLE没什么很大的区别在这段上。。请高手指点下。。谢谢

Answer 1

KRISADMIN."STATUS"."DESCRIPTION"这种写法是什么啊？

Answer 2

我括号没有少拉，有个很奇怪的。。
我全部语句放一条进行select就有ORA-00907的错误。。
但是随便找个地方移到到下一行就没错了。。为什么啊？
eg
SELECT FileID AS "ID", FILENO, TITLE, KRISADMIN."STATUS"."DESCRIPTION" AS "STATUS", KRISADMIN."SECURITYLEVEL"."DESCRIPTION" AS "GRADING", KRISADMIN."COMPARTMENT"."NAME" AS "NAME" FROM KRISADMIN."FILE" LEFT JOIN KRISADMIN."STATUS" ON KRISADMIN."FILE".STATUSID = KRISADMIN."STATUS".STATUSID LEFT JOIN KRISADMIN."SECURITYLEVEL" ON KRISADMIN."FILE".SECURITYLEVELID = KRISADMIN."SECURITYLEVEL".SECURITYLEVELID LEFT JOIN KRISADMIN."COMPARTMENT" ON KRISADMIN."FILE".COMPARTMENTID = KRISADMIN."COMPARTMENT".COMPARTMENTID WHERE (UPPER(FileNo) LIKE 'CEO%' OR UPPER(Title) LIKE '%CEO%'OR UPPER(KRISADMIN."STATUS"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."SECURITYLEVEL"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."COMPARTMENT"."NAME") LIKE '%CEO%')AND (FILEID IN (SELECT ChildID FROM table(cast(KRISADMIN.UDF_COMPTFILETREE('2','1')as KRISADMIN.ComptSubjFileTree_TBL)))  OR FILEID IN (SELECT ChildID FROM table(cast(KRISADMIN.UDF_SUBJFILETREE('2','1') as KRISADMIN.ComptSubjFileTree_TBL)))  OR FILEID IN (SELECT ChildID FROM ta
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     *
ERROR at line 1:
ORA-00907: missing right parenthesis

Answer 3

SELECT FileID AS "ID", FILENO, TITLE, KRISADMIN."STATUS"."DESCRIPTION" AS "STATUS", KRISADMIN."SECURITYLEVEL"."DESCRIPTION" AS "GRADING", KRISADMIN."COMPARTMENT"."NAME" AS "NAME" 
FROM KRISADMIN."FILE" LEFT JOIN KRISADMIN."STATUS" ON KRISADMIN."FILE".STATUSID = KRISADMIN."STATUS".STATUSID LEFT JOIN KRISADMIN."SECURITYLEVEL" ON KRISADMIN."FILE".SECURITYLEVELID = KRISADMIN."SECURITYLEVEL".SECURITYLEVELID LEFT JOIN KRISADMIN."COMPARTMENT" ON KRISADMIN."FILE".COMPARTMENTID = KRISADMIN."COMPARTMENT".COMPARTMENTID WHERE (UPPER(FileNo) LIKE 'CEO%' OR UPPER(Title) LIKE '%CEO%'OR UPPER(KRISADMIN."STATUS"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."SECURITYLEVEL"."DESCRIPTION") LIKE '%CEO%'OR UPPER(KRISADMIN."COMPARTMENT"."NAME") LIKE '%CEO%')AND (FILEID IN (SELECT ChildID FROM table(cast(KRISADMIN.UDF_COMPTFILETREE('2','1')as KRISADMIN.ComptSubjFileTree_TBL)))  OR FILEID IN (SELECT ChildID FROM table(cast(KRISADMIN.UDF_SUBJFILETREE('2','1') as KRISADMIN.ComptSubjFileTree_TBL)))  OR FILEID IN (SELECT ChildID FROM table(cast(KRISADMIN.UDF_PUBLICCOMPTFILETREE('1') as KRISADMIN.PublicComptFileTree_TBL))))ORDER BY "NAME",FILENO,TITLEno rows selected