I have 2 separate tables. I can’t seem to figure out a Select statement with a Join and Case condition that gives results based on changing account numbers. In Client_info table we have 6 digit account numbers which can now change to 7 or 8. Client Balance table is connected with Client Info via the acct_no field but the acct_no field here is a derivation of acct_no in Client info. In some cases, there are digits before and after the six digits. For example:

1. 261650 in client Info is represented by 102616508400 in Client Balance
2. 596649 in client Info is represented by 000596649 in Client Balance
3. 123456 in client Info is represented by 1234560001 in Client Balance
4. 1234567001 in client Info is represented by 1234567001 in Client Balance

enter image description here

Any help is appreciated!

My logic is based on matching the acct numbers on different tables based on length of account number in Client balance. Example:

1. Case ((Client_info.acct_no) = 6 AND LENGTH(Client_Balance.Acct_no) = 9) THEN SUBSTR(Client_Balance.Acct_no,4,6)
2. Case ((Client_info.acct_no) = 6 AND LENGTH(Client_Balance.Acct_no) = 12) THEN SUBSTR(Client_Balance.Acct_no,3,6)

It has to be a SINGLE SQL statement.

• Below is what I started testing but it doesn’t return any results. I believe this is because the compiler doesn’t recognize Client_info.acct_no = 6 since the Join condition is towards the end.

SELECT Client_Balance.Acct_no, Client_Balance.Acct_desc, Client_Balance.Acct_bal
FROM Client_Balance
JOIN Client_info ON
CASE
WHEN ((Client_info.acct_no) = 6 AND LENGTH(Client_Balance.Acct_no) = 9) THEN SUBSTR(Client_Balance.Acct_no,4,6)
END
= Client_info.acct_no;