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  iWay Software Product Forum on Focal Point    [CASE-OPENED] Pivot a source with unknown upper limit number of records
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] Pivot a source with unknown upper limit number of records
 Login/Join
 
Member
posted
Data Migrator Assistance needed for Pivoting a source with unknown upper limit number of records

Looking for any help available to be able to pivot a source table which has varrying number of records that need to be concatenated into a single record.

Source:
ISN SEQ Expl
1 1 A
1 2 B
1 3 C
2 1 D
2 2 E

Target (should look like)
ISN EXPL_OUT
1 ABC
2 DE

There could be more that 100 source records that need to be concatenated into a single record.
Thanks

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 8 | Registered: June 06, 2013Reply With QuoteReport This Post
<Kathryn Henning>
posted
Hi Meena,

I see that a case has been opened for this topic. Please keep us posted on the final solution from the case. Thank you in advance for sharing with all.

Regards,

Kathryn
 
Reply With QuoteReport This Post
Guru
posted Hide Post
I see from the hottrack case that the source is a DB2 table so you can take advantage of the DB2 function LISTAGG using DB_EXPR. Create your select statement so it reads:

SELECT
  ISN, 
  DB_EXPR (LISTAGG (EXPL, ' ') WITHIN GROUP (ORDER BY SEQ)) AS EXPL_OUT  
  FROM source
  GROUP BY ISN
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Clif for the recommendation. It looks like LISTAGG is available for DB2 9.7+, since we are currently on 9.1 this doesn't work for us. However we are moving to Oracle soon, and hopefully we will be able to use this function then. Loogks like this funtion is supported by Oracle 11g.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 8 | Registered: June 06, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Indeed LISTAGG is in DB2 9.7. Also ORACLE 11g Release 2. I'm glad that will work for you.

MySQL has a similar function GROUP_CONCAT(). Other databases though not so much.

DB_EXPR by the way requires DM 7.7.05. For earlier releases you can use a DBMS SQL Flow.
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
we have now moved to Oracle 11g, and i'm trying to use DB_EXPR(Listagg....). The field that I am aggregating is char 60 (A60V), and I am writing it to a TX50 field (oracle CLOB). When I use DB_EXPR(Listagg...), by default, the output format is A4000V.

when I execute this flow, I get an error stating...
SQL PASSTHRU - DATATYPE NOT SUPPORTED FOR EDA
Return Code = 1513.

I was able to get the flow to run, if i used CAST on the DB_EXPR expression, and changed the format to A2000V.

Limitation of 4000 char is concerning, but 2000 characters really isn't feasible for us. Is there another option we could use.

Thanks
Meena


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 8 | Registered: June 06, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Meena - This may point you in the right direction. https://techsupport.informatio...om/sps/81572017.html

I haven't worked with CLOBs in a while. I take it that that the 1513 error number is an Oracle error number. Please confirm.

Rhonda


WebFOCUS & ReportCaster 8.1.05, 7.7.03 - AIX/LINUX, FOCUS 7.6.13 MVS, Output PDF, XLS-XLSX, DOCX and HTML
 
Posts: 146 | Location: Atlanta, GA | Registered: May 31, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CASE-OPENED] Pivot a source with unknown upper limit number of records

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.