Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting numeric data from VARCHAR2 type

 
Gajen Pingalkar
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
In my application, I am using Oracle tables where I have one column. That column type is varchar2, there I am storing numeric value .
And my Persistent object variable is String.
In my DAO I am calling Criteria,


Here I am giving numeric 'Value' for comparing with ColumnName (Varchar2)
But I am not getting expected result.
Please suggest, how to get correct result. Here we can not change type of column or value in database.

Thanks in advance.
Gajendra
[ November 27, 2008: Message edited by: Gajen Pingalkar ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

But I am getting expected result.
Please suggest, how to get correct result. Here we can not change type of column or value in database.

I'm not sure I understand. What is happening and what do you expect to happen?
 
Gajen Pingalkar
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:

I'm not sure I understand. What is happening and what do you expect to happen?


Actually I want to say I am not getting expected results. Expected result are, If I am passing condition like columnName > 50. then criteria should return all records where column value is more than 50, but here criteria check with string i.e. (varchar2) and if ASCII is more than that, it get return those records..
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Expected result are, If I am passing condition like columnName > 50. then criteria should return all records where column value is more than 50, but here criteria check with string i.e. (varchar2) and if ASCII is more than that, it get return those records..

This sort of comparison cannot be done in the way you want it to behave with any char data type. The only way I can think of going it is to return everything in the table and doing your greater than in Java.
 
Gajen Pingalkar
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:

This sort of comparison cannot be done in the way you want it to behave with any char data type. The only way I can think of going it is to return everything in the table and doing your greater than in Java.


Thank for Reply.
As per Your comment, you mean to say retrieve all record from Table and apply greater than condition in Java. Actually Table could contain lots of Data, like more than 35,000 records. In that case retreiving all data is not effecient process. Please suggest any other way to achive this type of condition and also consider column could have string data like [A-Z or -,. +&# etc]

Thanks in advance
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Actually Table could contain lots of Data, like more than 35,000 records. In that case retreiving all data is not effecient process. Please suggest any other way to achive this type of condition and also consider column could have string data like [A-Z or -,. +&# etc]

Because this is Oracle, you could use the to_number function. Using functions in where clauses though will slow down your query too, but it might be to an acceptable amount. This will also stop your application being portable.

You would have to do this as a SQL query (its just not possible using Criteria or HQL). Assuming you have a nice DAO layer though, you should be able to hide the nastiness of the data model from the rest of your application.
 
Ram kovis
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
probably, you can try


[ December 01, 2008: Message edited by: Ram kovis ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic