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,

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.

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?

Do you have to do this in InfoAssist only or can you use FOCUS code?

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.

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,

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.

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
  

Just to confirm that using the LAST function as suggested here has solved my issue.
Many thanks for your great advice...