Re: Invalid Column Name (but only when run from some places)

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 22 Apr 2003 10:09:55 -0700
Message-ID: <92eeeff0.0304220909.5480ace5@posting.google.com>

nick.duddy_at_showgroup.com.au (Nickd) wrote in message news:<bad96ff0.0304212035.2f90cb80_at_posting.google.com>...
> Hi,
>
> I have a select statement which is causing me some grief. Basically,
> when run in SQL*Plus or TOAD, it works fine. However if I run it in a
> Java servlet I get an ORA-00904, "Invalid Column Name".(The query
> isn't too big so I have pasted it all below)
>
> What confuses me more, is that if I take out the;
>
> (TO_CHAR (nad.dep_date, 'dd/mm/yyyy')) dep_date_short
>
> and;
>
> (TO_CHAR (nad.arr_date, 'dd/mm/yyyy')) arr_date_short
>
> and replace them with just the field (eg nad.dep_date, nad_arr_date)
> then it works fine. I initially thought the TO_CHAR was the problem,
> but the other occurence of a more complex TO_CHAR stays in and works.
>
> I'm using 8.1.7.0.0 and had jdbc drivers from early 2002 and today
> tried (with no benefit) updating them to both the latest 8.1.7 and 9i
> drivers. Same results.
>
> I can't imagine the tomcat etc version (3.3) has anything to do with
> Oracle spitting back an error to a query. i have tried against 8.1.7
> on both Solaris and W2K.
>
> Finally - All I'm trying to do is get the dates returned in a format I
> want - if there is a better way to do it then I'm open to suggestions.
> i could do swap it about ni my java, but I'd rather just get the right
> data from the query in the first place.
>
> Thanks for reading - I hope someone can shed light on my error!
>
> Nick.
>
> *** Start Pasted query ***
> SELECT nad_invn.invono, nad.nights, nad_invn.trv, nad.hp_ref,
> nad.currcd,
> custvend.accno,
> (TO_CHAR ( nad.comm_loc
> * (1 + ABS (nad_art.vatcd * nad_art.gstcomm /
> 100)),
> 'FM99999.90'
> )
> ) comm_loc_gst,
> nad_art.gstcomm,
> (TO_CHAR (nad.dep_date, 'dd/mm/yyyy')) dep_date_short,
> (TO_CHAR (nad.arr_date, 'dd/mm/yyyy')) arr_date_short
> FROM nad_invn nad_invn,
> nad_commview nad,
> nadfin.custvend custvend,
> nad_invh nad_invh,
> nad_off nad_off,
> nad_invl nad_invl,
> nad_art nad_art
> WHERE ( (nad.accno = custvend.accno)
> AND (nad.invono = nad_invn.invono)
> AND (nad.invl_lineno = nad_invn.invl_lineno)
> AND (nad.invono = nad_invh.invono)
> AND (nad_invh.offno = nad_off.offno)
> AND (nad_invh.invono = nad_invl.invono)
> AND (nad_invl.invono = nad_invn.invono)
> AND (nad_invl.lineno = nad_invn.invl_lineno)
> AND (nad_invl.typeno = nad_art.artno)
> AND (custvend.accno = '10004484')
> )
> ORDER BY 9 ASC
> *** End pasted query ***

Your problem may be due to your Patch version (8.1.7.0.0) and/or jdbc driver version classesXX.zip. I compiled a quick code with statement like "select to_char(sysdate, 'mm/dd/yyyy') from dual",

import java.sql.*;
public class Test {

   public static void main (String args[])

      throws Exception {

      DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

      String url = "jdbc:oracle:oci8:@TEST817";
      Connection conn =
         DriverManager.getConnection (url, "user", "password");

      Statement stmt = conn.createStatement ();
      ResultSet rset = stmt.executeQuery ("select to_char(sysdate,
'mm/dd/yyyy') from dual");
      while (rset.next())
         System.out.println(rset.getString(1));
      rset.close();    
      stmt.close();    
      conn.close();

   }
}

and it worked fine. I used 8.1.7.3 and classes12.zip and ofcourse this was just a simple test from command line.

Upgrade to latest 8.1.7.x patchset... even if this is not your solution.

Regards
/Rauf Sarwar Received on Tue Apr 22 2003 - 12:09:55 CDT