[Chkenergy]

Brief history: Had to modify table, removed 2 columns, region and prospect id. These columns are now being pulled from another table. If I cut and paste the SQL into SQL Navigator, the SQL works fine. When I call the function from VB I now recieve the following error:

ORA-00904: invalid column name

904
java.sql.SQLException: ORA-00904: invalid column name

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:877)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2513)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2857)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:4303)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:642)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:80)
at com.cnr.pms.ejb.CNRPMSdrillEJB.total_started_for_region(CNRPMSdrillEJB.java:1388)
at com.cnr.pms.ejb.CNRPMSdrill_xzoeeu_EOImpl.total_started_for_region(CNRPMSdrill_xzoeeu_EOImpl.java:254)
at com.cnr.pms.ejb.CNRPMSdrillJavaEJB.c_select_region_counts(CNRPMSdrillJavaEJB.java:575)
at com.cnr.pms.ejb.CNRPMSdrillJava_jaj1h2_EOImpl.c_select_region_counts(CNRPMSdrillJava_jaj1h2_EOImpl.java:98)
at com.cnr.pms.ejb.CNRPMSdrillJava_jaj1h2_EOImpl_WLSkel.invoke(Unknown Source)
at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:477)
at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:108)
at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:420)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:353)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:144)
at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:415)
at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:30)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)

****ORIGINAL SQL*****
“select c.loc_desc, ” +
“a.region_id, ” +
“count(*) ” +
“from pms005_well_cost_mstr a, ” +
“pms003_well_dates b, ” +
“mfs022_region_mstr c ” +
“where a.well_id = b.well_no ” +
” and a.company_id = b.company_id ” +
” and a.well_bore = b.well_bore ” +
“and a.region_id = c.region_id ” +
“and ((to_char(b.actual_start_dt, ‘yyyy’) = ? ” +
“and (total_depth_dt IS NULL ” +
“or to_char(b.total_depth_dt, ‘yyyy’) = ?)) ” +
“or (b.actual_start_dt IS NOT NULL ” +
“and to_char(b.total_depth_dt, ‘yyyy’) = ?)) ” +
“group by c.loc_desc, pm100.region_id “;

*****NEW SQL**** basically, just added the pm100 table’s region id
“select c.loc_desc, ” +
“pm100.region_id, ” +
“count(*) ” +
“from pms005_well_cost_mstr a, ” +
“pms003_well_dates b, ” +
“mfs022_region_mstr c, ” +
“pms100_gplat_detail pm100 ” +
“where a.well_id = b.well_no ” +
” and a.company_id = b.company_id ” +
” and a.well_bore = b.well_bore ” +
” and a.company_id = pm100.company_id ” +
” and a.well_id = pm100.well_id ” +
” and a.well_bore = pm100.well_bore ” +
“and pm100.region_id = c.region_id ” +
“and ((to_char(b.actual_start_dt, ‘yyyy’) = ? ” +
“and (total_depth_dt IS NULL ” +
“or to_char(b.total_depth_dt, ‘yyyy’) = ?)) ” +
“or (b.actual_start_dt IS NOT NULL ” +
“and to_char(b.total_depth_dt, ‘yyyy’) = ?)) ” +
“group by c.loc_desc, pm100.region_id “;

ANY IDEAS?

[Riyad Kalla]

Moving to OT > Soft Dev

[Author]

Posts