problem joining on imported unix column

Database
Enthusiast

problem joining on imported unix column

Table A has the value 'abc123' Table B has the value 'abc123'

I confirm this by

SELECT * FROM tableA a WHERE value='abc123'
and
SELECT * FROM tableB b WHERE value='abc123'

and both queries return results

however when i do:

select
a
.*
from
tablea a
join
tableb b
on trim(trailing from a.value) = trim(trailing from b.value)

nothing returns.

now the string I am dealing with in one of the these tables was imported over from unix and I made sure to strip out the whitespace, etc. but I can't figure out what I'm doing wrong.

Thru char_length, I've also verified that the column in question in each table is 32 chars in length.

9 REPLIES
Junior Contributor

Re: problem joining on imported unix column

Strange,

what is returned when you UNION both values?

SELECT value, char2hexint(value) FROM tableA a WHERE value='abc123'
UNION
SELECT value, char2hexint(value) FROM tableB b WHERE value='abc123'

Dieter

Enthusiast

Re: problem joining on imported unix column

Dieter:

That does work!  What could be the difference between 'abc123' and 'abc123' that is stored in a teradata table?

Junior Contributor

Re: problem joining on imported unix column

"does work" is almost as good as "doesn't work" :-)

What is returned by that query? One row or two rows? And for UNION ALL?

Can you show the actual output?

Btw, you never need to join on TRIM(TRAILING) as string comparison does't care about trailing blanks. But a leading blank would matter, you might try TRIM(BOTH) instead.

Dieter

Enthusiast

Re: problem joining on imported unix column

Dieter:

One row is returned.  Here is the output:

3230376239303736313430306135663134343037343536376662616333623037                                                                                                                                                                                                                                                                                                                                                   again it's a 32 varchar string I'm trying to match up.

thanks for the tip about trim.

derek                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

Enthusiast

Re: problem joining on imported unix column

...and union all produces 15 rows exactly like the string above.

Junior Contributor

Re: problem joining on imported unix column

Hi Derek,

so you're searching for '207b90761400a5f144074567fbac3b07'?

And this exists in both tables? For 100% shure?

In the beginning i thought there might have been '20'xb and '80xb', space and no-break space, or non-printing chars which look the same, but these are only latin chars and digits.

You might open an incident.

Dieter 

Enthusiast

Re: problem joining on imported unix column

Dieter:

thanks for the quick reply.  yes, I am searching for 

207b90761400a5f144074567fbac3b07

I'm sure (or as sure as can be) b/c when I search for the string (ie select * from tablea where val=

207b90761400a5f144074567fbac3b07

i get back a result.

Junior Contributor

Re: problem joining on imported unix column

If it exists in both tables following SQL should return exactly one row:

select * from 
(select val from tablea where val= '207b90761400a5f144074567fbac3b07') as a
full join
(select val from tableb where val= '207b90761400a5f144074567fbac3b07') as b
on a.val = b.val

Otherwise i don't know...

Dieter
Teradata Employee

Re: problem joining on imported unix column

Could you share the column definitions from the table? I'm wondering about differences in character set, upper/lower case attributes,...