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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Joining three tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Joining three tables
 Login/Join
 
Ramya
Gold member
posted
Hi,

I face some challenges in joining three tables and fetching out columns from all the three tables

JOIN COL1 FROM TBL1 TO COL1 FROM TBL2 AS J0
END
JOIN COL2 FROM TBL2 TO COL2 FROM TBL3 AS J1
END

Now i am, able to fetch columns from TBL1 and TBL2 but not from TBL3.

TABLE FILE TBL1
PRINT
TBL3.A
END
-RUN

This gives me the folloeing error. Unrecognized field 'A'.

Any help would be highly appreciated.

Regards,
Ramya

This message has been edited. Last edited by: <Kathryn Henning>,


WebFocus 7702
HTML
 
Posts: 73 | Registered: January 02, 2012Report This Post
Shakila Subhan
Silver Member
posted Hide Post
JOIN COL1 FROM TBL1 TO COL1 FROM TBL2 AS J0
END
JOIN COL2 FROM TBL1 TO COL2 FROM TBL3 AS J1
END


WebFOCUS 8.0.08 - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
Windows, All Outputs
 
Posts: 40 | Registered: April 02, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
JOIN COL1 IN TBL1 TO COL1 IN TBL2 AS J0
END
JOIN J0.SEG01.COL2 IN TBL1 TO COL2 IN TBL3 AS J1
END


Creates a relationship such as
TBL1
|
TBL2
|
TBL3


Instead of
JOIN COL1 IN TBL1 TO COL1 IN TBL2 AS J0
END
JOIN COL2 IN TBL1 TO COL2 IN TBL3 AS J1
END


Which is a relationship such as
    TBL1
     |
    _ _
   |   |
TBL2  TBL3


Shakila suggestion is good until COL2 exist in TBL1.

It's always a good idea to use GUI to create JOINs. That way you'll have it properly defined.

This message has been edited. Last edited by: MartinY,


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Ramya & Shakila,

The simple syntax for JOIN is:
JOIN hfld1 IN hostfile 
TO [ALL]
crfield IN crfile AS joinname
END

Notice: one does not use FROM.

Since you do not use ALL this mean that you are in a one-to-one relationship.
So, if COL2 is in TBL2 then you will get Martin's first relationship. If it is in TBL1 you will get the second one.
It does not depend on the JOIN command.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Ramya
Gold member
posted Hide Post
Hi Martin,

Thanks for your reply.

I used the join as suggeted by you.

JOIN COL1 IN TBL1 TO COL1 IN TBL2 AS J0
END
JOIN J0.SEG01.COL2 IN TBL2 TO COL2 IN TBL3 AS J1
END

I am unable to print it.

TABLE FILE TBL1
PRINT
TBL2.COL
END
-RUN
-EXIT

Again it gives me the error, unrecognized field name TBL2.COL

Any suggestions on this?

Thanks,
Ramya


WebFocus 7702
HTML
 
Posts: 73 | Registered: January 02, 2012Report This Post
MartinY
Virtuoso
posted Hide Post
Hi Ramya,

Since you're using tables and fields from a JOIN you must refer them as from the join.

So it must look like this:
TABLE FILE TBL1
PRINT
J0.SEG01.COL
END
-RUN
-EXIT

Where you have to specify if it's from the parent (TBL1), the first child (which is your first join with TBL2 but now referred as J0) and also the segment's name (SEG01 in my example). Then if you have fields from TBL3, you need to reference them as J1.SEG01.xxxxx.

Hope that help.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Tony A
Expert
posted Hide Post
quote:
JOIN COL1 IN TBL1 TO COL1 IN TBL2 AS J0ENDJOIN J0.SEG01.COL2 IN TBL2 TO COL2 IN TBL3 AS J1END

Ramya,

That is not the join that Martin suggested.

Check his post against what you are using, correct and retry. Hint - see bold piece below.

JOIN COL1 IN TBL1 TO COL1 IN TBL2 AS J0
END
JOIN J0.SEG01.COL2 IN TBL2 TO COL2 IN TBL3 AS J1
END

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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Joining three tables

Copyright © 1996-2020 Information Builders