Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Concatenate multiple values from 1 to many join
Go
New
Search
Notify
Tools
Reply
  
[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, 2012Reply With QuoteReport 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 8203, Unix, Windows
 
Posts: 1187 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do you have to do this in InfoAssist only or can you use FOCUS code?


WebFOCUS 8203, Unix, Windows
 
Posts: 1187 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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: 77 | Registered: November 08, 2010Reply With QuoteReport 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: 181 | Location: Infobuild India | Registered: August 28, 2015Reply With QuoteReport 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, 2012Reply With QuoteReport 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: 181 | Location: Infobuild India | Registered: August 28, 2015Reply With QuoteReport 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, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.