*RIGHTJUST<\/a> being able to allow for filtering and record selections when running apps.\u00a0 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).\u00a0 This tech blog post will cover this specific scenario.<\/p>\n\n\n\nImplementation<\/h2>\n\n\n\n
To do so, simply build the app as desired, being sure to join out to the second table.\u00a0 Compile the app and at runtime, the second table’s records should not be brought in since the different justifications result in no matches.\u00a0 Go back to the interface and open Application Properties.\u00a0 Turn debugging on by changing the radio button next to debug from No to Yes.\u00a0 Run the app again, only this time, copy the SQL statement at the bottom of the page.\u00a0 It should look something like this:<\/p>\n\n\n\n
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<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
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(<\/span>T02.”INVNO2″)<\/span> ORDER BY T01.”VEND” ASC,T01.”INVNO” ASC<\/p>\n\n\n\nFinally, 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.<\/p>\n\n\n\n
Note:<\/strong> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"Overview Many people know about *RIGHTJUST being able to allow for filtering and record selections when running apps.\u00a0 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).\u00a0 This tech blog post will…<\/p>\n","protected":false},"author":3,"comment_status":"closed","ping_status":"open","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[],"ht-kb-tag":[],"class_list":["post-892","ht_kb","type-ht_kb","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/892","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=892"}],"version-history":[{"count":4,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/892\/revisions"}],"predecessor-version":[{"id":12189,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/892\/revisions\/12189"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=892"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-category&post=892"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-tag&post=892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}