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

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 ***