aspose file tools*
The moose likes JDBC and the fly likes Mapping Varchar to Number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Mapping Varchar to Number" Watch "Mapping Varchar to Number" New topic
Author

Mapping Varchar to Number

Anand Loni
Ranch Hand

Joined: Jan 20, 2006
Posts: 150
Hi,

I have two tables, in first table Column Country is defined as VarChar, and in another table column Country is number. I have predefined set of Country names that will go in first table like US, FR etc and each country is given some number like US is 0, FR is 1.

My problem is, I am writing a query where I am finding data from second table with same Country. Here I should convert value US to 0, FR to 1 etc.

How should I write query that will replace country value with number ?

Regards,
Anand


~ Anand,
SCJP 1.5, SCWCD 1.5
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I have two tables, in first table Column Country is defined as VarChar, and in another table column Country is number

So these two values are completely unrelated?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Anand Loni
Ranch Hand

Joined: Jan 20, 2006
Posts: 150
those are related, only difference is that one is in varchar(String representation) and other is in number.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

When I say "related" I am using the term as it is understood in relational database, i.e. there is a constraint between the two to maintain the relationship. Since I can't work out how you would do that based on the description you have given I was hoping you could clarify you model. Where is the relationship defined?
Anand Loni
Ranch Hand

Joined: Jan 20, 2006
Posts: 150
I am very much new to these (database, sql queries), so I dont have much idea about what you said.

But I will tell you that, two tables has listname and country common, I dont know why they designed these two tables with such country column. One in varchar and one in number.

thanks for your efforts on this topic
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ok. Can you fully describe your two tables? Can you tell us if one has a foreign key to another (i.e. how they are related)?

What you've got sounds like denormalized data. That is unless one table is called country and the number in the other table is a foreign key to it?
[ October 18, 2007: Message edited by: Paul Sturrock ]
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Anand Loni:
Hi,

I have two tables, in first table Column Country is defined as VarChar, and in another table column Country is number. I have predefined set of Country names that will go in first table like US, FR etc and each country is given some number like US is 0, FR is 1.

My problem is, I am writing a query where I am finding data from second table with same Country. Here I should convert value US to 0, FR to 1 etc.

How should I write query that will replace country value with number ?

Regards,
Anand


Oracle use
to_number(varchar column here)

PostgreSQL/DB2/MySQL
cast(varchar column here AS newDataTypeHere)
[ October 18, 2007: Message edited by: Paul Campbell ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mapping Varchar to Number