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  iWay Software Product Forum on Focal Point    [CASE-OPENED] Pivot a source with unknown upper limit number of records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] Pivot a source with unknown upper limit number of records
 Login/Join
 
Meena
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, 2013Report 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
 
Report This Post
Clif
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


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Meena
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, 2013Report This Post
Clif
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.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Meena
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, 2013Report This Post
Rhonda
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, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders