Win a copy of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Mapping Varchar to Number

 
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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?
 
Anand Loni
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
those are related, only difference is that one is in varchar(String representation) and other is in number.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic