Monday, March 12, 2012

Joining Different Datatypes

I'm pretty sure this answer is a 'no' but I wanted to post something just in case.

I'm working with a database that is a back-end for an application. For upgrade compatibility issues, I'm not allowed to alter the database in any fashion.

However, my boss asked me to create a query that relies on information from two different tables. The information I need (the keys unique to each of the rows) is in two different datatypes. The information is the same, but one table stores it as a number, the other as a varchar.

Is there any way to query the information I want joining these two datatypes? Or is there a procedure I can create that converts one column to the datatype I need?

Any help would be greatly appreicated. Thanks in advance.Originally posted by xenovah
I'm pretty sure this answer is a 'no' but I wanted to post something just in case.

I'm working with a database that is a back-end for an application. For upgrade compatibility issues, I'm not allowed to alter the database in any fashion.

However, my boss asked me to create a query that relies on information from two different tables. The information I need (the keys unique to each of the rows) is in two different datatypes. The information is the same, but one table stores it as a number, the other as a varchar.

Is there any way to query the information I want joining these two datatypes? Or is there a procedure I can create that converts one column to the datatype I need?

Any help would be greatly appreicated. Thanks in advance.
The answer will be DBMS-specific, but it is possible to convert data from one datatype to another. In Oracle for example:

WHERE a.numcol = TO_NUMBER(b.charcol)

Beware that the TO_NUMBER would fail and raise an exception if any data in b.charcol was not convertible to a NUMBER.

No comments:

Post a Comment