Focal Point
[SOLVED] Join not working

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9387085876

September 25, 2015, 03:04 PM
Pondog
[SOLVED] Join not working
I have a simple inner join between two table that isn't working. Table A has various year values, e.g. 2011, 2015, 2012, etc. Table B has only one value, 2015. When I do an Inner Join between these two tables I expect to get only the 2015 records out of Table A; however, I'm getting everything.

Now, If I add the Table B field to my report, then I get only 2015 items.

The report only works correctly if I add the field from Table B or I add a WHERE statement. I thought the Inner Join would have negated the need for the WHERE statement. Why doesn't it?

Tony

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8.1.05
Windows, All Outputs
September 25, 2015, 03:11 PM
MartinY
Reverse your JOIN.

The way it works in FOCUS is not the same as a SQL Join.

Instead of (which you probably did):
JOIN
 INNER field IN TableA
    TO field IN TableB Tag J1 AS J1
END


Consider:
JOIN
 INNER field IN TableB
    TO field IN TableA Tag J1 AS J1
END


Join Child to Parent instead of Parent to Child.

In a JOIN, if you do not include any field from the joined table in your TABLE FILE...END it's like having no join for Focus.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
September 25, 2015, 03:32 PM
Pondog
Hey MartinY,
I found a reference to something in IB Tech support (hottrack 60642543) that says if there isn't a file used in Table B then WebFOCUS won't recognize the join.

figures

Thanks,
Tony


WebFOCUS 8.1.05
Windows, All Outputs
December 23, 2016, 02:15 PM
noelmcavoy
quote:

I found a reference to something in IB Tech support (hottrack 60642543) that says if there isn't a file used in Table B then WebFOCUS won't recognize the join.


This may have been obvious to some but in case someone misses it like I did on first read...

If you don't use a FIELD from the joined table (TableB) then the join is ignored.


WebFOCUS 8.1.0.5
Windows 10, all output
December 23, 2016, 02:34 PM
RSquared
JOIN FILE file AT field TO ALL FILE file AT field AS name
WHERE condition

From Walter Flood's Hidden Gems


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
January 12, 2017, 12:38 PM
Terri1977
Hello, I need assistance with a JOIN. I uploaded an EXCEL spreadsheet into WebFocus 8.1, and the administration created a mas. I want to join it to another table in Oracle, but I keep getting an error even with the correct key. Please assist.


WebFOCUS 8
Windows, All Outputs
January 17, 2017, 09:27 AM
Diane
Terri977,

Please provide the masters and join statement


WebFOCUS 8.1.0.5m
January 17, 2017, 12:41 PM
eric.woerle
I wouldn't recommend directly joining an Excel file to your Oracle table. It will be really inefficient.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2