Thanks for the reply. Actually I did without having the to_number() but still it is giving me the issue. I am trying to do the following inside select statement: select (case when #endDate# - least(#startDate# + (nDays * #granularity#), #endDate#) < #granularity# then #endDate# - (#granularity# - 1) else least(#startDate# + ((nDays) * #granularity#), #endDate#) end )
when I use to_number() in the difference it says: Got interval of time expected NUMBER when I dont use to_number() in the diff of two dates: I get Expected Interval of time got NUMBER. ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER I would appreciate if some one can help in solving the issue. Thanks, Kiran --- On Sun, 8/24/08, Joakim Olsson <[EMAIL PROTECTED]> wrote: From: Joakim Olsson <[EMAIL PROTECTED]> Subject: Re: java.sql.SQLException: ORA-01722: invalid number To: "iBatis Forum" <[email protected]> Date: Sunday, August 24, 2008, 2:57 AM When you subtract two dates the result already is a number. Using to_number on the result will make Oracle do an implicit conversion to CHAR and then trying to convert it back again. Depending on your NLS-parameters that implicit conversion will break stuff. Regards, Joakim On Sat, 2008-08-23 at 19:36 -0700, kiran vuppla wrote: > > > I am getting the following error when I try to convert the diff of > dates to to_number(). Can some one help me will be appreciated. I ran > the same thing from ORACLE TOAD and it is giving the expected > results. > > > > The SQL I am trying to convert the diff of two dates to number: > > floor(to_number(#endDate# - least(#startDate# + ((nDays+1) * > #granularity#), #endDate#))) > > ERROR: > > > --- Cause: java.sql.SQLException: ORA-01722: invalid number > > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201) > > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139) > > at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567) > > at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541) > > at > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118) > > at org.springframework.orm.ibatis.SqlMapClientTemplate > $3.doInSqlMapClient(SqlMapClientTemplate.java:298) > > at > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209) > > ... 72 more > > Caused by: java.sql.SQLException: ORA-01722: invalid number > > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145) > > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) > > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) > > at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) > > at > oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) > > at > oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1120) > > at > oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1144) > > at > oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962) > > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243) > > at > oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415) > > at > oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3520) > > at > org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169) > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > > at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) > > at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) > > at java.lang.reflect.Method.invoke(Unknown Source) > > at > com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62) > > at $Proxy56.execute(Unknown Source) > > at > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185) > > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221) > > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189) > > ... 78 more > > > > Thanks > Kiran > > >
