![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
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 | ||
|
Master |
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 8202M, Unix, Windows | |||
|
Member |
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 | |||
|
Master |
Do you have to do this in InfoAssist only or can you use FOCUS code? WebFOCUS 8202M, Unix, Windows | |||
|
Gold member |
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 | |||
|
Platinum Member |
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 ENDThis 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 | |||
|
Member |
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 | |||
|
Platinum Member |
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 | |||
|
Member |
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 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|