Not showing the rows which is having the digits < 6 !!!

[girishlc]

Hi All,
Can anybody look into this below query? I’m running these below queries on Oracle SQL developer.

SELECT * FROM BATCH.ITEM@SCPOREADDB WHERE ITEM LIKE '%82128%'

SELECT * FROM EXCEED.CRECOVER@EXERPTDB206 WHERE PROD_ID LIKE '%82128%'

SELECT C.PROD_ID, I.UDC_BDMNAME, I.ITEM FROM EXCEED.CRECOVER@EXERPTDB206 C INNER JOIN BATCH.ITEM@SCPOREADDB I ON TO_CHAR(TRIM(C.PROD_ID)) = TO_CHAR(TRIM(I.ITEM)) AND I.ITEM LIKE '%82128%'

First two queries are working fine, but when I join using INNER JOIN condition it is not showing the ITEM/PROD_ID which is less than 6 digits.

Where PROD_ID is of type CHAR(18bytes) and ITEM is of type VARCHAR2(50Char)

It will display the rows which is having 6 digit Item & Prod_id but if it is < 6 digits those rows are not displaying while joining.

Thanks in advance...

[r035198x]

1.) Are you using Oracle? You posted this in the PostgreSQL forum
2.) Why did you trim and to_char in the query with the join when you didn't do it on the one without?