All Forums Database
dkrantz11 5 posts Joined 01/13
02 Oct 2013
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.

dnoeth 4628 posts Joined 11/04
02 Oct 2013

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

Dieter

dkrantz11 5 posts Joined 01/13
02 Oct 2013

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

dnoeth 4628 posts Joined 11/04
02 Oct 2013

"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

Dieter

dkrantz11 5 posts Joined 01/13
02 Oct 2013

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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

dkrantz11 5 posts Joined 01/13
02 Oct 2013

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

dnoeth 4628 posts Joined 11/04
02 Oct 2013

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 

Dieter

dkrantz11 5 posts Joined 01/13
02 Oct 2013

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.

 

dnoeth 4628 posts Joined 11/04
02 Oct 2013

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
 

Dieter

ToddAWalter 316 posts Joined 10/11
02 Oct 2013

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

You must sign in to leave a comment.