Hi,
you are right. I tried to reomove it and it works under Oracle.
I talked to my collegue that developed the application in Oracle and he said
that may occur problems with indexes using implicit conversions.
Anyway I will try to investigate code and see if i can remove "to_number".
But I'm not sure i can remove it everywhere.
Thanks
Fabio
----- Original Message -----
From: "Zabach, Elke" <[EMAIL PROTECTED]>
To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>
Sent: Tuesday, June 15, 2004 12:40 PM
Subject: AW: Parameter substitution - to_number
Hi,
A little bit off the topic:
Oracle NEEDS the TO_NUMBER? As far as I know, Oracle has this
implicit char <--> number-conversion at any place a user can imagine.
And you told us that this implicit conversion is not used in your case
when using real Oracle?
Elke
SAP Labs Berlin
> -----Urspr�ngliche Nachricht-----
> Von: Fabio Pinotti [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 15. Juni 2004 12:08
> An: [EMAIL PROTECTED]
> Betreff: Parameter substitution - to_number
>
> Hi
> we have the following behaviour when passing parameters from java
> code. The sql statement is something like (executed in oracle mode):
>
> SELECT
> *
> FROM
> TABLE1 b
> inner join TABLE2 a on (b.e_key=a.key)
> WHERE
> b.D_START=to_DATE(?, 'dd/mm/yyyy') AND
> b.column1=to_number(?) AND
> b.e_key=to_number(?)
>
> The java code with parameter passing is
>
> PreparedStatement st= cn.prepareStatement(sqlString);
> st.setString(1,"26/05/2004");
> st.setString(2,"1");
> st.setString(3,"200027");
>
> We get, on maxdb 7.5.01.00, the following error
>
> com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3016]: Invalid numeric
> constant
>
> Note that all colums involved in the query (except b.D_START) are
> "fixed(9)".
>
> At the first time we thought the problem was due to "to_number" function,
> so we tried not to use it. The query is now:
>
> SELECT
> *
> FROM
> TABLE1 b
> inner join TABLE2 a on (b.e_key=a.key)
> WHERE
> b.D_START=to_DATE(?, 'dd/mm/yyyy') AND
> b.column1=? AND
> b.e_key=?
>
> and it does work!.
>
> Unfortunatly we need "to_number", because we use the same sql on a oracle
> db. So we mustn't remove it.
> The strange thing is that excluding the last row from query ("
> b.e_key=to_number(?)") and obviuosly not passing the third parameter,
> everything works fine. So it seems that "to_number" confilcts with join
> condition, which refers to the same column.
>
> Thanks in advance
> Fabio
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
