Overview
Many people know about *RIGHTJUST being able to allow for filtering and record selections when running apps. However, there is also a way to join two tables together on a field that is justified differently (left-justified in one table and right-justified in the other). This tech blog post will cover this specific scenario.
Implementation
To do so, simply build the app as desired, being sure to join out to the second table. Compile the app and at runtime, the second table’s records should not be brought in since the different justifications result in no matches. Go back to the interface and open Application Properties. Turn debugging on by changing the radio button next to debug from No to Yes. Run the app again, only this time, copy the SQL statement at the bottom of the page. It should look something like this:
SELECT T01.”VEND”, T01.”INVNO”, T01.”BLAH”, T02.”INVNO2″, SUBSTRING(T02.”INVNO2″,1,4) FROM MRCWORKLIB.”TABLEA” T01 left outer join MRCWORKLIB.”TABLEB” T02 ON T01.”VEND”=T02.”VEND” AND T01.”INVNO”=T02.”INVNO2″ ORDER BY T01.”VEND” ASC,T01.”INVNO” ASC
In order to join the two tables together, we will use the TRIM() function to remove any padding and left-justify the fields. To do so, find the field that is left justified (INVNO2 from TABLEB, in my example). Immediately before the field type TRIM( and immediately after type ). Using my previous example, the SQL code would now look like:
SELECT T01.”VEND”, T01.”INVNO”, T01.”BLAH”, T02.”INVNO2″, SUBSTRING(T02.”INVNO2″,1,4) FROM MRCISSUES.”ISSUE17085″ T01 left outer join MRCISSUES.”ISS_170852″ T02 ON T01.”VEND”=T02.”VEND” AND T01.”INVNO”=TRIM(T02.”INVNO2″) ORDER BY T01.”VEND” ASC,T01.”INVNO” ASC
Finally, paste this modified SQL code into your app by opening Application Properties and navigating to the SQL tab. Find the text box labeled SQL Statement and paste this modified SQL code into this box. Click OK. Now, at runtime, the two tables will be joined properly since the fields are both left-justified.
Note: If both fields are left-justified AND right-justified within the same column, it may be necessary to TRIM both fields. This is certainly possible. Just keep in mind that using the TRIM function is the same as left-justifying the field.