Strange behaviour joining to concatenated variable

Database

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

right join

(
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.
Tags (2)
1 REPLY
Junior Supporter

Re: Strange behaviour joining to concatenated variable

Please post the EXACT SQL, the one you posted is INCORRECT.

There is a comma missing after 'txn.tx_acct_no' and the real name for the column is 'tx_acct_no', so the join on 'on txn.acct_no = ac.acct_no' will fail.

Also, post the DDL for the tables (datatypes are important). Note that you issued "substr(acct_no,1,1) = 2 ", without single quotes around the '2', when the acc_no seems to be a CHAR o VARCHAR column.

Cheers.

Carlos.