Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join to X or Y

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Join to X or Y
 Login/Join
 
DW Marker
Silver Member
posted
For the life of me I can't figure this out. I need to do a join between two tables (both RDBMS tables) where I join field A on table 1 to Field B OR field C on table 2. This should be equality checks.

Basically I want the join to be WHERE (T1.A = T2.B) OR (T1.A = T2.C) and I need to handle it within WebFOCUS.

This message has been edited. Last edited by: DW Marker,


WF 7.6.4
Windows XP and UNIX
 
Posts: 41 | Registered: November 26, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
How about using CONDIONAL JOIN?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Moogle
Guru
posted Hide Post
If I was building this, I would probably do it like this:

-Join A to B
-Table file that pulls what I need from A & B and holds it.
-Clear Joins
-Join A to C
-Table file that pulls what I need from A & C and appends it to the hold file from the first step
-table file on the combined hold file that generates my output

That's what I'd do.

Cheers,

Joey


-WebFOCUS 8.2.01 on Windows
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
DW Marker
Silver Member
posted Hide Post
I haven't ever had the need to append to a hold file. Can you let me know what the steps are to do that?


WF 7.6.4
Windows XP and UNIX
 
Posts: 41 | Registered: November 26, 2008Report This Post
Moogle
Guru
posted Hide Post
Sure! What you do is start with a FILEDEF, which would look something like:

FILEDEF {HOLDFILE NAME} {physical file name.ftm}

Then you do a table file and hold it as the same name as in your filedef:

TABLE FILE CAR
SUM
SALES
BY BODYTYPE
ON TABLE HOLD AS {HOLDFILE NAME}
END

At this point you have a temporary file sitting around, ready for the next step.

Do another filedef, with the same syntax, but with "(APPEND" at the end. Note that there is no close bracket:

FILEDEF {HOLDFILE NAME} {physical file name.ftm} (APPEND

TABLE FILE CAR
SUM
SALES
BY BODYTYPE
ON TABLE HOLD AS
END

Now if you do a table file on the hold file name, it will contain both sets of data from your first and second queries.

Cheers,

Joey


-WebFOCUS 8.2.01 on Windows
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Tony A
Expert
posted Hide Post
Using what Joey suggests is the pure WebFOCUS method of achieving what you require, but I would suggest that this is a time when SQL passthru is ideal -

SQL SQL...
  select column1
       , column2 etc. etc.
    from table1 T1
       , table2 T2
   where T1.A = T2.B
      or T1.A = T2.C
;
TABLE FILE SQLOUT
PRINT *
END

If your experience is SQL then why not use your strengths and those of WebFOCUS?

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
DW Marker
Silver Member
posted Hide Post
My only question regarding using SQL passthru is does that bypass the security in the DBA Security files? If so that is a no go for us as that is how it was determined we would handle security for our 500 external clients as opposed to setting up database views for each of them.


WF 7.6.4
Windows XP and UNIX
 
Posts: 41 | Registered: November 26, 2008Report This Post
<JG>
posted
Passthru dos not use the master so yes it would bypass the DBA
 
Report This Post
GinnyJakes
Expert
posted Hide Post
Have you looked at the CONDITIONAL JOIN syntax? It should do what you want.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
You can write SQL that uses the WebFOCUS metadata (Master, Access) which I assume will not bypass DBA security in the Master. If you set the SQL Engine, the SQL is "passthru", if you do not set the SQL engine, the SQL uses the WebFOCUS metadata.

Though, I think Ginny's suggestion best fits what you want to do.

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
DW Marker
Silver Member
posted Hide Post
Thanks everyone for their direction. The conditonal join did what I needed.


WF 7.6.4
Windows XP and UNIX
 
Posts: 41 | Registered: November 26, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join to X or Y

Copyright © 1996-2020 Information Builders