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] Concatenate multiple values from 1 to many join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Concatenate multiple values from 1 to many join
 Login/Join
 
Member
posted
Hi Everyone.
Sorry if this is a bit basic but I have been asked to produce a report using INFOASSIST on two Oracle tables where the join is 1 to many.
The 'many' side can be anything from 0 rows to around 10 rows.
The output needs to be a single row based on entries from the '1' table with a column containing the concatenated values from the 'many' table.
First, is this actually possible.
Secondly, I'm afraid I have no idea where to start on this.
I have tried looking for similar examples, tried using MAX function to get an idea of how many in each join and read the manuals numerous times, all without any success.
I'm using Webfocus/Infoassist 8, Windows 7 and Oracle 11g
Thank you...

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


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Virtuoso
posted Hide Post
First: Of course it's possible.
Second: Can you show us an example of psudo data in the two tables and how you'd like them to look after you've concatenated the values? I'd like to make sure I get what you're describing using some basic examples.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
Thanks for the quick reply.
As an example:
Table1 has 5 fields, F1 to F5
Table2 has 3 fields f1 to f3
They are joined on F2 and f2 with table2 having say 2 entries for 1 row in table1.

The output should be 6 columns, F1 to F5 plus the two values of f3 from table2 concatenated.

eg:
table1 F1 name
F2 ID
F3 Address
F4 Phone
F5 email
table2: f1 order1
f2 ID
f3 item1
f1 order2
f2 ID
f3 item2

Output:
Name ID Address Phone email (item1, item2)

ie: 1 row per ID

Is this OK?


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Virtuoso
posted Hide Post
Do you have to do this in InfoAssist only or can you use FOCUS code?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gold member
posted Hide Post
Should be able to with a define field using LAST to build a list. Can SUM to make sure is one row per ID/ F2 and do MAX. on the list to get the longest one.

The define could look something like
ITEM_LIST/A2000V=IF F2 EQ LAST F2 THEN LAST ITEM_LIST || ', ' | F3 ELSE F3;


Course since LAST means it is looking at the previous row's data if it isn't sorted there can be issues. Can print out all of the lines though to double check that it is adding the values as desired.

If you want the () around the value would do that as a separate define that just uses the list field.


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
Platinum Member
posted Hide Post
Hi Tcox,

Try this solution ,

Table1 :- This table has one row for one ID)
CREATE TABLE [dbo].[table1](
	[name] [nchar](10) NULL,
	[ID] [int] NOT NULL,
	[Address] [nchar](10) NULL,
	[Phone] [nchar](10) NULL,
	[email] [nchar](30) NULL
) ON [PRIMARY] 
 


Table 2:- This table has multiple items and order(rows) for a ID,

 CREATE TABLE [dbo].[Table2](
	[order1] [nchar](10) NULL,
	[ID] [int] NOT NULL,
	[item1] [nchar](10) NULL,
	[order2] [nchar](10) NULL
) ON [PRIMARY] 



WF Code:-
 JOIN
 TABLE1.TABLE1.ID IN table1 TO MULTIPLE TABLE2.TABLE2.ID IN table2 TAG J0
AS J0
END
DEFINE FILE TABLE1
item/A200=
IF( TABLE1.TABLE1.ID EQ LAST TABLE1.TABLE1.ID ) THEN LAST ITEM |','| J0.TABLE2.ITEM1 ELSE J0.TABLE2.ITEM1;
item2/A400=
IF( TABLE1.TABLE1.ID EQ LAST TABLE1.TABLE1.ID ) THEN LAST J0.TABLE2.item |','| J0.TABLE2.ITEM1 ELSE J0.TABLE2.item;

END
TABLE FILE TABLE1
SUM
    MAX.J0.TABLE2.item2
BY   TABLE1.TABLE1.ID
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
 

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


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Member
posted Hide Post
Thanks All for taking the time to reply.

I am restricted to using INFOASSIST on this but I like the idea of using the LAST function so I'll try that today and let you know how it goes.


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report This Post
Platinum Member
posted Hide Post
Tcox,

You can apply this scenario in Info-Assist as well,

ENGINE INT CACHE SET ON


-*COMPONENT=Join_J001
JOIN TABLE1.TABLE1.ID IN DEMO/TABLE1
TO MULTIPLE TABLE2.TABLE2.ID IN DEMO/TABLE2 TAG J001 AS J001
END
-*COMPONENT=Define_table1
DEFINE FILE demo/table1
 item/A200=IF( TABLE1.TABLE1.ID EQ LAST TABLE1.TABLE1.ID ) THEN LAST  ITEM |','|  J001.TABLE2.ITEM1  ELSE J001.TABLE2.ITEM1 ;
 item2/A400=IF( TABLE1.TABLE1.ID EQ LAST  TABLE1.TABLE1.ID   ) THEN LAST  item  |','|  J001.TABLE2.ITEM1  ELSE item ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE demo/table1
SUM item2
BY TABLE1.TABLE1.ID
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN
  


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Member
posted Hide Post
Just to confirm that using the LAST function as suggested here has solved my issue.
Many thanks for your great advice...


Release 8
Windows 7
Excel/HTML/PDF
Oracle 11g
 
Posts: 16 | Registered: October 23, 2012Report 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] Concatenate multiple values from 1 to many join

Copyright © 1996-2020 Information Builders