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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Report Display same results multiple times instead of once.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Report Display same results multiple times instead of once.
 Login/Join
 
Member
posted
Hi,

I have a two tables join, when i run the report i am getting the same results repeated over and over. Table A is Join to Table B.

What am i doing wrong?

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


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Member
posted Hide Post
Could you provide more informations about your code and your data?

This is a simple join:

  

TABLE1
-----------
001 AAA
002 BBB
003 CCC


TABLE2
-----------
001 XXX 
003 FFF

JOIN CLEAR *
JOIN KEY IN TABLE1 TO KEY IN TABLE2 AS J1

You get:

001 AAA 001 XXX
002 BBB
003 CCC 003 FFF




FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Member
posted Hide Post
hi,

This is my code.

JOIN
FCT_PURCH.FCT_PURCH.SUPPLIER_KEY IN FCT_PURCH TO MULTIPLE
DIM_SUPPLIER.DIM_SUPPLIER.SUPPLIER_KEY IN DIM_SUPPLIER TAG J0 AS J0
END

This is the results i am getting.

04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Member
posted Hide Post
Is 04258 the key value?

Is this value one time in table FCT_PURCH and 4 times in table SUPPLIER_KEY?



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Member
posted Hide Post
Hi,

Yes, its DIM_SUPPLIER


v7.6, Windows 2003, MS SQL 2003
 
Posts: 12 | Location: Mississauga | Registered: December 22, 2008Report This Post
Virtuoso
posted Hide Post
That's why you're getting multiple values you've joined one record in FCT_PURCH "TO MULTIPLE" records in DIM_SUPPLIER. So you get all the records you've joined to. It's called the multiplicative effect. If you only want one line, you've either got to use a SUM or remove the "TO MULTIPLE." If you don't know what the "TO MULTIPLE" is doing in your Join, I would refer you back to the basic documentation.


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
Virtuoso
posted Hide Post
Amit,

You also have not told us which fields you are showing in your report - are they originating from both tables or from just one? Are the keyfield(s) of both tables in the report? If not, then include them and you would have to see different field values for the keys in the second table. This is of course assuming that the join-to file has keys defined in ms sql. If no keys are defined, then duplicate values may occur, but then they will still have to be present in the join-to table.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report 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] Report Display same results multiple times instead of once.

Copyright © 1996-2020 Information Builders