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

[Brad Gronli]

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

[Walter Blood]

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 ;

[Walter Blood]

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!

[Walter Blood]

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 ;

[Brad Gronli]

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;