CODEWITHSUNDEEP
sqloracle-database
Trevor D
Trevor D

Reputation: 743

PL/SQL Developer: joining VARCHAR2 to NUMBER?

Here's where I am:

Here is a simplified version of my working query:

select
A.ITM_CD
,B.COST
,B.SIZE
,B.DESCRIPTION
,A.HOLD_REASON

from
TABLE1 a
left join TABLE2 b on a.ITM_CD = to_char(b.ITM_CD)

This query returns a list of item codes and hold reasons, but just blank values for the cost, size, and descriptions. And I did confirm that TABLE2 contains values for these fields for the returned codes.

UPDATE: Here are pictures with additional info.

I selected the following info from ALL_TAB_COLUMNS--I don't necessarily know what all fields mean, but thought it might be helpful

Information

TABLE1 sample data
TABLE1 image

TABLE2 sample data
TABLE2 image

Upvotes: 0

Views: 3002

Answers (3)

MT0
MT0

Reputation: 167981

You can convert the TABLE1.ITM_CD to a number after you strip any leading zeros and filter out the "MIXED" values:

select A.ITM_CD
      ,B.COST
      ,B.SIZE
      ,B.DESCRIPTION
      ,A.HOLD_REASON
from  ( SELECT * FROM TABLE1 WHERE ITM_CD <> 'MIXED' ) a
      left join TABLE2 b
      on TO_NUMBER( LTRIM( a.ITM_CD, '0' ) ) = b.ITM_CD

Upvotes: 1

Mennan
Mennan

Reputation: 81

You can trim leading zeros from your string column.

select A.ITM_CD ,B.COST ,B.SIZE ,B.DESCRIPTION ,A.HOLD_REASON from TABLE1 a left join TABLE2 b on trim(LEADING '0' FROM a.ITM_CD ) = to_char(b.ITM_CD)

Upvotes: 0

user5683823
user5683823

Reputation:

This is a SQL (and really a database) problem, not PL/SQL. You will need to fix this - fight your bosses if you have to. Item code must be a Primary Key in one of your two tables, and a Foreign Key in the other table, pointing to the PK. Or perhaps Item code is PK in another table which you didn't show us, and Item code in both the tables you showed us should be FK pointing to that PK.

You don't have that arrangement now, which is exactly why you are having all this trouble. The data type must match (it doesn't now), and you shouldn't have values like 'MIXED' - unless your business rules allow it, and then the field should be VARCHAR2 and 'MIXED' should be one of the values in the PK column (whichever table that is in).

In your case, the problem is that the codes in VARCHAR2 format start with a leading 0, so if you compare to the numbers converted to strings, you never get a match (and in the outer join, the match is always assumed to be to NULL).

Instead, when you convert your numbers to strings, add leading zero(s) like this:

...on a.ITM_CD = TO_CHAR(b.ITM_CD, '099999')

Upvotes: 0

Related Questions