I have accidentally stumbled upon a way to create a cross jo...

[Peter Vazny]

I have accidentally stumbled upon a way to create a cross join in InfoAssist. This is useful for applying the MacGyver technique, or if for some reason you need to add some information from an unrelated table to every record. For example you have a table with company name that you need to pull into a report.

Normally your only options are Inner and Left Outer joins when using InfoAssist. To work around it, you need to create a filter based join and modify the existing Where condition to always return true. It does not matter that the join arrow points to an unrelated field**, the only thing that matters are the filter conditions.

 

 

image.png750501 21.8 KB

 

 

image.png788175 13.7 KB

 

**Caveat: I dont think that the fields on which you join matter at all when you use DBMS and the request is translated to SQL. It seems to matter if the data source is WebFocus files, but only as far as the fields needing to be from the correct segment. For example the employee table, can have multiple address records per employee, so as long as the AT field is from the address segment, in this case TYPE, it will work with pointing to any TO field in the ggsales table. I picked Alpha fields. I am not sure if it works across types too.

Sample code:

-*COMPONENT=Join_J001

JOIN FILE ibisamp_new/employee AT EMPLOYEE.ADDRESS.TYPE

TO MULTIPLE FILE ibisamp_new/ggsales AT GGSALES.SALES01.CITY TAG J001 AS J001

WHERE J001.SALES01.SEQ_NO LE 3;

END

 

-*COMPONENT=Define_employee

DEFINE FILE ibisamp_new/employee

Address_LN/A20=IF J001.SALES01.SEQ_NO EQ 1 THEN EMPLOYEE.ADDRESS.ADDRESS_LN1 ELSE IF J001.SALES01.SEQ_NO EQ 2 THEN EMPLOYEE.ADDRESS.ADDRESS_LN2 ELSE EMPLOYEE.ADDRESS.ADDRESS_LN3 ;

END

 

TABLE FILE ibisamp_new/employee

SUM MAX.Address_LN

BY EMPLOYEE.EMPINFO.EMP_ID

BY EMPLOYEE.ADDRESS.TYPE

BY J001.SALES01.SEQ_NO

END

 

-RUN

 

I removed the styling and settings that InfoAssist inserts for easier readability.

 

This seems to be an undocumented feature. At least I have not been able to find a way to do it up until now. So it could go away or in some way break in the future.

[Manoj Chaurasia]

Hi Peter,

Thanks for Sharing!!