Strange behaviour joining to concatenated variable
I've had some problems with joining to a concatenated variable and although I've fixed the problem I'd like to understand what happened here.
As background, in a transaction table we have the first digit of the account number was dropped for a couple of days and so we have a number of records where the account number needs a single digit added to the front.
We basically wanted to do a really simple query to find out how many transactions have been made on an account. However, when a join on the reconstructed account number didn't work.
The abstraction of the code is below:
select ac.acct_no, txn.tx_acct_no txn.counter from ( select acct_no from accts where acct_no = '12345' ) as ac
( select case when substr(acct_no,1,1) = 2 then '1'||acct_no else acct_no end as tx_acct_no, count(*) as counter from transactions group by 1 ) as txn
on txn.acct_no = ac.acct_no
I was expecting this result (basically 2 rows for the 1 account): acct_no tx_acct_no counter 12345 12345 10 12345 12345 5
What I'm actually getting is: acct_no tx_acct_no counter 12345 12345 10 ? 12345 5
I found a couple of solutions that work and some that don't although they all seem like the should. So the ones that don't work are: 1. Casting ... on cast(txn.acct_no as integer) = cast(ac.acct_no as integer) --> 2620 error: bad character
2. Trimming ... on trim(txn.acct_no) = trim(ac.acct_no) --> No error but incorrect result - also I tried trimming the variables when they are concatenated
Now for what did work: 1. Substring ... select case when substr(acct_no,1,1) = 2 then '1'||substr(acct_no,1,4) else acct_no end as tx_acct_no ...
2. Hashing ... on hashrow(txn.acct_no) = hashrow(ac.acct_no)
I've checked with the best TD people we have here and they didn't know what was going on so if anyone can shed some light on this it would be really appreciated.