×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

mock outer/left join

mock outer/left join

mock outer/left join

(OP)
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?

RE: mock outer/left join

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.

RE: mock outer/left join

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close