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?
TonyThis 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