CODEWITHSUNDEEP
sqloracle-databasejoin
jijo
jijo

Reputation: 815

Match exact values on oracle join

Here it goes.
I have two tables say, Application and Report.
There tables have a common column(appId, externalAppId respectively) which can be joined to find unique values.
My problems is when I join these 2 tables, I'm getting values which I don't really want.

Sample values

Application   Report
No appId      ReportNo ExternalAppId
1  123         1        123
2  456         2        00000123
3  789         3        321

So when I say Application.appId = Report.ExternalAppId in my where condition, it is returning me rows of 123 and 00000123 from Report table .
The leading zeros are not taken into account in the join.
I need result with exact matches only.
In this case, the first row alone.The cause of the issue I think is the appId is number and ExternalAppId is varchar.
I cant change this also. Is there any workaround ?
I have seen regex which can remove the leading zeros and then match, but just want to know if there is a better solution.
ie can I specify that the join will work only for the values with exact match ?

Upvotes: 1

Views: 511

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Oracle can only compare two values of the same datatype. I can't stress this enough. In fact most languages can compare two values only if they are the same datatype. Relations in math will also be defined with objects of the same type (so that you can define transitivity, reflexivity...). There's also the saying with oranges and apples: don't try to compare them.

So when you ask Oracle to compare two values of different datatypes, an implicit conversion will take place. In most cases you should avoid these conversions, since the rules of which datatype will be chosen over the other can be quite complex and (like in this case) will often produce bugs because you will incorrectly guess which type will win. You should rely on explicit conversions (conversions that you specify).

I assume that Application (appId) is a NUMBER and Report (ExternalAppId) is of type VARCHAR2. In this case Oracle chose to convert ExternalAppId to a NUMBER, and in the NUMBER space, 00123=123 because numbers have no format.

You should have written instead your join condition as:

to_char(application.appId) = report.externalAppId

Upvotes: 4

Related Questions