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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
One-to-many Join
 Login/Join
 
Expert
posted
In a one-to-many RDBMS join, is there any way of retrieving only one row per parent segment - other than using BY LOWEST/HIGHEST 1 column-name-in-child-segment?

Thanks.


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
Expert
posted Hide Post
Francis,

Off the top of my shiny head Wink

SET ALL = OFF?
- or -
TABLE FILE parent
SUM MAX.childfield
    MIN.childfield
    FST.childfield
    LST.childfield
BY parentfield

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
Expert
posted Hide Post
Take the ALL off of the JOIN so that it is unique? Just an idea. You can do the SQL trace to see what SQL you get.

Do you want the highest or lowest or just the first one?

Also, while I know you use SQL Server, you need to be careful with direct operators as not all are passed to all RDBMS adapters.


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
Virtuoso
posted Hide Post
As the others already stated, which one do you want to get.
It's like as if you say I have 6 children, and tonight I'm going to the circus, but I have only two tickets, one for me and one for.....the one how has first finished his meal...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
I can only afford to take one child out and I don't care which one.

I'll try the SET ALL=OFF setting.

Thanks.


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
Expert
posted Hide Post
Francis,

With my aplogies to Tony, I don't think SET ALL=OFF will work. It is off by default so if your query is not doing what you expect now, explicitly coding it won't help.

Can you try taking the all off of the JOIN and let us know what happens?


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
Gold member
posted Hide Post
You will need to use the aggregates of min or max.

JOIN KEY IN FILE1 TO KEY IN FILE2
TABLE FILE FILE1
SUM
MIN.FILE2.FIELD1
BY FILE1.FIELD1
END

Otherwise you could turn off optimization and force focus to do the join, or create a hold file from file1 and then join the hold file up which would also force a focus managed join.



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
Expert
posted Hide Post
i agree with Ginny...i think the ALL out of the JOIN will bring you back just your firstborn child, the others having run away from home to join that circus.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Guru
posted Hide Post
Coding a one-to-one JOIN against FOCUS databases will return only one matching row from the child file regardless of the true nature of the JOIN. However, I don't think you can force an rdbms to treat a one-to-many relationship as a one-to-one relationship. Even if you code the JOIN without the ALL it will return all rows and then you need to try to work with the prefix operators or the BY 1 type of syntax.

I'd experiment with the SET OPTIMIZATION OFF. You should see a FOCUS managed JOIN in the traces which could allow the FOCUS one-to-one JOIN behaviour. Be aware it will do multiple select statements... one for each table. The other setting to try is SET OPTIMIZATION FOCUS .


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Expert
posted Hide Post
how about that! hmm. thanks,P.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
I originally had SET ALL=ON in the program and was getting a lot more rows than I expected. I SET ALL=OFF and reduced the number of rows but I am still getting more than I should be.

Over the years I have disciplined myself to writing the most optimized code as possible, so my fingers would find it very hard to type "SET OPTIMIZATION OFF"!

I may give it a try.

Thanks,


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
Virtuoso
posted Hide Post
Ginny's original response was the answer. You don't need to change settings with SETs. This is even simpler if you don't care which record it is. If you remove the ALLfrom the JOIN statement, as soon as it finds a single cross-referenced record, it quits looking for more. So you get one child record per parent.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Master
posted Hide Post
select distinct....
OR
select [unique field[s]],
[some aggregation function such as MAX() of fact field 1],
[some aggregation function such as MAX() of fact field 2]
from
x inner join y on x.field = y.field
group by
[unique field[s]]


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Master
posted Hide Post
Darin, I hate to disagree with a Guru, but Gizmo has the correct answer. To return one row per parent in a one-to-may RDBMS join, you must do something that creates a GROUP BY line. To do that, you have to do MIN.field or MAX.field, just as GIZMO states. The other option, as Gizmo states is to set optimization off, which I almost never recommend. Consider the following examples where KDSCLINT is the parent and CL_CLID is a unique key and KDSRFLCL is the child table where there can be many occurances of CL_CLID/RC_CLID:
 JOIN CL_CLID IN KDSCLINT TO RC_CLID IN KDSRFLCL
 TABLE FILE KDSCLINT
 PRINT CL_FNM CL_LNM RC_REFER_ID
    BY CL_CLID
 WHERE CL_CLID EQ 1746707
    ON TABLE HOLD
 END

returns the following
SELECT T1."CL_ID",
       T1."FRST_NME",
       T1."LST_NME",
       T2."REFER_ID"
  FROM KIDSPROD.TCLIENT T1,
  KIDSPROD.TREF_CLIENT T2
 WHERE (T2."CL_ID" = T1."CL_ID")
   AND (T1."CL_ID" = 1746707)
 ORDER BY T1."CL_ID";

 0 NUMBER OF RECORDS IN TABLE=        6  LINES=      6


Second example:
 JOIN CL_CLID IN KDSCLINT TO RC_CLID IN KDSRFLCL
 TABLE FILE KDSCLINT
   SUM MAX.CL_FNM MAX.CL_LNM MAX.RC_REFER_ID
    BY CL_CLID
 WHERE CL_CLID EQ 1746707
    ON TABLE HOLD
 END

returns the following
SELECT T1."CL_ID",
       MAX(T1."FRST_NME"),
       MAX(T1."LST_NME"),
       MAX(T2."REFER_ID")
  FROM KIDSPROD.TCLIENT T1,
       KIDSPROD.TREF_CLIENT T2
 WHERE (T2."CL_ID" = T1."CL_ID")
   AND (T1."CL_ID" = 1746707)
 GROUP BY T1."CL_ID"
 ORDER BY T1."CL_ID";

 0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1


Notice the GROUP BY. That will get you the single row. Note, FST. and LST. will work but you get the following:
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2505) A SEGMENT IN THE STRUCTURE IS NON-KEYED:  : TCLIENT
(FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED


The only effect that ALL being ON or OFF has is that with ALL being ON, you will still get a parent if there are no children. WebFOCUS now generates the outer join where as in earlier releases, it did not.

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


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
jgelona, thanks for the explanation.


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
Expert
posted Hide Post
that was very instructive. thank you jg




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
quote:
As the others already stated, which one do you want to get.
It's like as if you say I have 6 children, and tonight I'm going to the circus, but I have only two tickets, one for me and one for.....the one how has first finished his meal...


So not the first or the last will join you but the oldest or the youngest....

What happens if there are no kids??

Do you get the parents, or would they be missing in this report.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
FrankDutch, that's where the SET ALL comes in. Set ALL to OFF and if there are no children, the parent row is dropped. Set ALL to ON or PASS and you will get the parent even if there are no children.

A lot of my reporting is done by dates and there are many times when I want the first or last. Since Oracle doesn't have a first or last operator like FST. or LST. and I have to use a 2 step process. First step is to collect and sort the data, hold the data, then use a second step on the HOLD file using FST. or LST. Sometimes, depending on the requirements, I will code an SQL PREPARE preparenm and TABLE FILE preparenm and use the FST. and/or LST. operators. This works because the database returns the answer set already sorted.

Much of my code came from FOCUS for HP-UX. One of the manuals we got was "Interface to Oracle Users Manual". I highly recommend it. Chapter 8 - Interface Reporting Techniques, explains a lot. There are some slight differences now on how the interface works now, i.e. when this manual was published, FOCUS would not generate outer joins when one used SET ALL=ON, now it does. I haven't checked to see if there is a current manual.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders