mock outer/left join

[danhauer]

I am a newbie to Focus, but with experience in several other db applications. I am going *crazy* trying to do a "many-to-many" or "left" or "outer" (you get the picture) JOIN in Focus. Does anyone have any ideas, or any way around it?

 

[esearing]

WebFocus does not allow many to many joins, not sure if Focus does. You may want to use the Match/Merge.

Another alternative is to create a HOLD table which has the unique values that you are joining and make two passes through the tables.

TABLE FILE firstable
SUM fields
BY uniquefield
ON TABLE HOLD AS UNIQLIST
END

JOIN UNIQLIST.uniquefield IN UNIQLIST
TO ALL nextTable.field IN nextTable
AS J1
END

TABLE FILE UNIQLIST
PRINT fields
BY field
END
Eric

If you create something idiot proof,
Nature will create a better idiot.

 

[esearing]

You can also use SQL Joins in your FEX. Below is an example:


SQL
SELECT XECHAIN.CHAIN_KID
,XECHAIN.CHAIN_ID
,XECHAIN.CUSTOMER_ID
,XEPROGRM.PROGRAM_KID
,XEPROGRM.CONTRACT_ITEM_KID
FROM XECHAIN
LEFT JOIN XEPROGRM ON XECHAIN.CHAIN_KID = XEPROGRM.CHAIN_KID
;
TABLE
ON TABLE HOLD AS XECUST
END
-*
TABLE FILE XECUST
PRINT...
Eric

If you create something idiot proof,
Nature will create a better idiot.