All Forums Database
claire 1 post Joined 12/15
03 Dec 2015
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]

Fred 1096 posts Joined 08/04
04 Dec 2015

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.

You must sign in to leave a comment.