Hi,

Would appreciate a quick suggestion on the below issue.

I have two MFDs A (HOST) and B (Cross Referenced). I am using the below join in the fex file. B has only data for Year (2017, 2018) and Month (Aug, 2017 to Feb 2018). After the Join I expected to get data for above mentioned months and year only. But Instead I get for all the Years (from 2012) and months, which are present in Host file. When I select columns from CF MFD, it does not show anything. It appears the join is not happening. But why?

All the webfocus help docs on joins says I should get the expected result as it is a inner join. But this is not working.

JOIN
INNER A.DF_YEAR
AND A.DF_MONTH
IN A TO MULTIPLE
B.YEAR
AND B.MONTH
IN B TAG J1 AS J1
END

TABLE FILE A
PRINT
     A.DF_YEAR
     A.DF_MONTH
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
     INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END  

This message has been edited. Last edited by: FP Mod Chuck,

In your TABLE request you only reference columns from table A. If you add a column from table B you'll get what you want. If you don't want to see the column from table B you can add a NOPRINT. The JOIN is only forced when you reference columns from both tables.

quote:

Originally posted by BabakNYC:
In your TABLE request you only reference columns from table A. If you add a column from table B you'll get what you want. If you don't want to see the column from table B you can add a NOPRINT. The JOIN is only forced when you reference columns from both tables.

BabakNYC,

I had tried with fields from CF MFD as well. But I still get years other than 2017 and 2018. That means it is doing left outer join. But as per the code, it should not.

Sandeep

Try making it a UNIQUE join...

JOIN
INNER A.DF_YEAR
AND A.DF_MONTH
IN A TO UNIQUE
B.YEAR
AND B.MONTH
IN B TAG J1 AS J1
END

quote:

Originally posted by FP Mod Chuck:
Sandeep

Try making it a UNIQUE join...

JOIN
INNER A.DF_YEAR
AND A.DF_MONTH
IN A TO UNIQUE
B.YEAR
AND B.MONTH
IN B TAG J1 AS J1
END

Chuck,

I did that but I still get unexpected year data.
The DF_YEAR and DF_MONTH in the A mfd are defined columns in the MFD. Does this have anything to do with it?

 JOIN
INNER A.DF_YEAR
AND A.DF_MONTH
IN A TO UNIQUE
B.YEAR
AND B.MONTH
IN B TAG J1 AS J1
END
-* File: IBFS:/mdart/WFC/Repository/Staging/Report1.fex Created by WebFOCUS AppStudio
TABLE FILE A
PRINT
     A.DF_YEAR
     J1.B.YEAR
     A.DF_MONTH
     J1.B.MONTH
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 

Sandeep

Please post both Master files in use so I can compare them...