Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

I have a need to join table A to table B using two fields an...


Brad Gronli

Recommended Posts

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...