INNER JOIN based on another field AS400

Question

I am maintaining an old system where the structure and the design of the tables are not created correctly. I know this is an "ugly" situation but I need to deal with it.

I have a table TABLEA containing multiple fields. Two of them represents the MODEL (MODEL1 and MODEL2). Based on status (STATUS), MODEL1 and MODEL2 have the following values:

1. STATUS = "A" THEN MODEL1="the model" AND MODEL2=""
2. STATUS <> "A" THEN MODEL1="" AND MODEL2="the model"

I need to perform a query where I am joining TABLEA with another table TABLEB on the model. According to the status, I need to join the tables with the non-empty model field.

I could resolve it by concatenating MODEL1 and MODEL2:

SELECT * 
FROM TABLEA A INNER JOIN TABLEB B
ON (A.MODEL1 || A.MODEL2) = B.MODEL;

but I am wondering if there is an elegant way to do it using an IF condition. Any other solution is welcome.

UPDATE - SAMPLE DATA

Answer 1

I like jmarkmurphy's solution better, but here is another option, by using left joins.

SELECT CASE WHEN b1.col1 = '' THEN b2.col1 ELSE b1.col1 END ......
FROM tablea a
LEFT JOIN tableb b1
ON a.model1 = b1.model
LEFT JOIN tableb b2
ON a.model2 = b2.model

But, if at all possible, a chang in schema is the best option:

• Adding a new a.model column with all nonempty values of both model1 and model2 and join on it.

• Using a materialized view like:

  SELECT a.*, CASE WHEN a.model1 = '' THEN a.model2 ELSE a.model1 END AS model

  FROM tablea a

• If there are no indexes on a.model1 or a.model2 which can improve query performance, you can use the previous view as a nonmaterialized, possibly inline, view.

Answer 2

You could use union all like this:

select * 
  from tablea a
    inner join tableb b
      on a.model1 = b.model and a.status = 'A'
union all
select * 
  from tablea a
    inner join tableb b
      on a.model2 = b.model and a.status <> 'A'