Complex Join using concat

fellipesg

Member
I have a query that gets rows from a double join in the same table, with different criteria. This is the join:
SELECT ... FROM F5547006
LEFT JOIN F03B11 B11A ON B11A.RPVR01 = 'B'||TAVR02 OR B11A.RPVR01 = 'R'||TAVR02
LEFT JOIN F03B11 B11B ON B11B.RPVR01 = 'B'||TAVR01 OR B11B.RPVR01 = 'R'||TAVR01;

The RPVR01 field, from F03B11, starts with 'B' or 'R'. In Business Design Aid, I can't join them because I need to concat 'B' OR 'R' to TAVR02 and TAVR01. The problem is, besides concat, in (...) part, I need to compare B11A.DOC to B11B.DOC, B11A.DCT to B11B.DCT and so on... How can I write this Join using Report Design Aid, in order to have, in the same time, B11A and B11B fields available to be able to compare them?
 
Top