java.sql.SQLException:ORA-00904

[abhishek...@gmail.com]

Hi All,

I am getting below error while executing my query in java program
however when I run the same query on Toad/ sqlplus the query runs fine
resulting the desired results.

java.sql.SQLException: ORA-00904: : invalid identifier

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1333)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:617)
at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1606)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1764)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1813)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:417)
at
org.apache.jsp.rptjobsubmit_jsp._jspService(rptjobsubmit_jsp.java:703)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:92)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)

Any thoughts on the probable reasons about the same.

My query is: "SELECT * FROM (SELECT COMP_CODE,COMP_CODE AS
APP_COMP_CODE, APPL_EMAIL,CLIENT_CODE,CLIENT_STAFF, CLIENT_NAME,
DEPT_CODE, DEPT_CODE AS APP_DEPT_CODE,GETTYPEDESC(COMP_CODE,DEPT_CODE,
'DEPT') AS DEPTNAME, GETSTAFFNAME(APPL_EMAIL) AS
STAFF_NAME,GETCCCODE(APPL_EMAIL) AS
CC_CODE,PROD_CODE,BIZ_DIV_CODE,SUM(T) AS AMT FROM ( SELECT
A.COMP_CODE,A.APPL_EMAIL,
C.CLIENT_CODE,C.CLIENT_STAFF,C.CLIENT_NAME,D.DEPT_CODE, D.BIZ_DIV_CODE,
E.PROD_CODE,ROUND(SUM(C.AMT_ALLOC)/GETSTAFFTOT(A.CL_GRP_REF_NO,
C.CL_REF_NO),2) AS T FROM ETS_CL_MAIN A, ETS_CL_DTLS B,
ETS_CL_CLIENT_SPL C, ETS_MAS_DEPT_LST D, ETS_MAS_PROD_AREA_DEPT_LST E
WHERE A.CL_GRP_REF_NO = B.CL_GRP_REF_NO AND B.CL_GRP_REF_NO =
C.CL_GRP_REF_NO AND B.CL_REF_NO = C.CL_REF_NO AND A.COMP_CODE =
D.COMP_CODE AND A.DEPT_CODE = D.DEPT_CODE AND A.COMP_CODE =
E.COMP_CODE(+) AND A.DEPT_CODE = E.DEPT_CODE(+) AND
UPPER(C.CLIENT_STAFF) IN (SELECT UPPER(D.CONTACT_NAME) FROM
ETS_CL_CLIENT_SPL C, ETS_MAS_CLIENT_CONTACT_LST D WHERE D.FM_FLG = 'Y'
AND C.CLIENT_STAFF = D.CONTACT_NAME AND C.CLIENT_CODE = D.CLIENT_CODE)
AND B.CLT_CODE = 'EN' AND UPPER(B.UPLD_STATUS) = 'COMPLETED' and
UPLD_SAP_DT BETWEEN TO_DATE('01-Apr-2001','dd-mm-yyyy') AND
TO_DATE('25-Apr-2006','dd-mm-yyyy') GROUP BY
A.COMP_CODE,A.APPL_EMAIL,CLIENT_CODE,CLIENT_NAME,CLIENT_STAFF,GETSTAFFTOT(A.CL_GRP_REF_NO,
C.CL_REF_NO), D.DEPT_CODE, D.BIZ_DIV_CODE,E.PROD_CODE ) A GROUP BY
COMP_CODE,APPL_EMAIL, CLIENT_CODE,CLIENT_STAFF, CLIENT_NAME, DEPT_CODE,
BIZ_DIV_CODE, PROD_CODE ) B WHERE AMT > 1000"

My environment details are:
Oracle 9.2.0.3.0
Jdk 1.4.7

I would appreciate any help in this regard.

Best Regards,
Abhishek

[steved...@gmail.com]

That error message means there is a column or function in your
statement that does not exist (or is not available the querying user).
Are you sure you are connecting with the same user in your java that
you are in TOAD/SQL*PLUS?

Regards,

Steve