Brad Gronli Posted January 7, 2022 Share Posted January 7, 2022 I have a need to join table A to table B using two fields and it needs to have a filter on a field in table B. This is something I can do in an SQL tool (DbVisualizer), but seem to be unable to do so in the WebFOCUS GUI. I can set the filter outside of the join, but this causes rows to be missed, when there is not a row on table B. The join should look something like: LEFT OUTER JOIN FILE TABLEA AT TABLEA.TABLEA.FIELDA AND TABLEA.TABLEA.FIELDB TO UNIQUE FILE TABLEB AT TABLEB.TABLEB.FIELDA AND TABLEB.TABLEB.FIELDB TAG J0001 AS J0001 WHERE TABLEB.TABLEB.DATE EQ 20990101; END Link to comment Share on other sites More sharing options...
Walter Blood Posted January 8, 2022 Share Posted January 8, 2022 The Join should be JOIN LEFT_OUTER TABLEA AT TO UNIQUE/MULTIPLE TABLEB AT <any field in TABLEB WHERE TABLEA.FIELD1 EQ TABLEB.FIELD2 AND TABLEA.FIELD2 EQ TABLEB.FIELD2 AND TABLEB.FIELD3 EQ some value ; Link to comment Share on other sites More sharing options...
Walter Blood Posted January 8, 2022 Share Posted January 8, 2022 This is a FOCUS language conditional JOIN. If you are interested in more about JOIN, Walter Brengel will be talking about JOIN at this months FOCUS Friday, Jan 17 at 11:30 am. Join us then! Link to comment Share on other sites More sharing options...
Walter Blood Posted January 8, 2022 Share Posted January 8, 2022 Cleaning up some typos on the example: JOIN LEFT_OUTER FILE TABLEA AT TO INIQUE/MULTIPLE FILE TABLEB AT WHERE TABLEA.FIELD1 EQ TABLEB.FIELD1 AND TABLEA.FIELD2 EQ TABLEB.FIELD2 AND TABLEB.FIELD3 EQ literal ; Link to comment Share on other sites More sharing options...
Brad Gronli Posted January 10, 2022 Author Share Posted January 10, 2022 In the GUI, I changed the JOIN to only JOIN on a single field, then added filters on the second field and on the date field. This looks to have done the trick and here is what the JOIN now looks like (names changed to protect the innocent). JOIN LEFT_OUTER FILETABLEA.FIELDA TO UNIQUE FILE TABLEB.FIELDA TAG J005 AS J005 WHERE TABLEA.FIELDB EQ J005.TABLEB.FIELDB; WHERE J005.TABLEB.DATE EQ 20990101; Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now