Case join using substring of data

Database
N/A

Case join using substring of data

Hi - I fully understand that this is not the best way to organize data.  However, this is how the data already exists, and i want to run a query and coul duse some help!  I have field aa in TABLE AA, and if i take a substring of field aa it will match perfectly to field bb of TABLE BB.  and once matched, I get lots of wonderful information from TABLE BB.  the catch: this substring is variable length.  Can anyone help me figure out a join, or some sort of "temp column" or something which will help me get this connection to the data i long for in TABLE BB?  Suggestions please!!  Thank you!

Sample code:

Select AA.aafields, BB.MyHopeFulBBFields

from 

AA, left join BB on

case when UPPER(SUBSTR(AA.aa, LENGTH(AA.aa),1)) = BB.bb then BB.othergreatdata

when UPPER(SUBSTR(AA.aa, LENGTH(AA.aa)-1,2)) = BB.othergreatdata

when UPPER(SUBSTR(AA.aa, LENGTH(AA.aa)-2,3)) = cc.string then BB.othergreatdata

when UPPER(SUBSTR(AA.aa, LENGTH(AA.aa)-3,4)) = cc.string then BB.othergreatdata

Else [i_don't_know_what_would_go_here_for_a_left_join]

1 REPLY
Teradata Employee

Re: Case join using substring of data

You can't use CASE in this way. You could potentially use OR:

... AA left join BB ON UPPER(SUBSTR(AA.aa, LENGTH(AA.aa),1)) = BB.bb OR UPPER(SUBSTR(AA.aa, LENGTH(AA.aa)-1,2)) = BB OR ...

Or write separate queries for each of the join conditions and UNION [ALL] the results.