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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
mock outer/left join
- Thread starter danhauer
- Start date
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.
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.
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.
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.
- Status
Similar threads
- Locked
- Question