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  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Move duplicated records from row view to across column view

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Move duplicated records from row view to across column view
 Login/Join
 
Member
posted
Move duplicated records from row view to across column view

I am very new to the FOCUS world and would like some direction. I am using an older version of Mainframe FOCUS (i.e., not WebFOCUS) and I am not sure if I can do the following within the FOCUS environment.

I table consists of 3 fields (Invoice, Part Number and Part Description) and because multiple part numbers can be on the same invoice, the table has duplicated invoices. I would like my results table to display only 1 invoice per row (below is an example).

What is the best approach to accomplish this? I have done this via SQL (script below).

Thank you in advance for the assistance.

Table:

INVOICE PART_NBR PART_DESC
12345 215487 AAABBCC
12345 245178 BBAACC
12346 234578 BBCACA
12456 324512 ABABAB
12457 234578 BBCACA
12457 245178 BBAACC

Wanted Results:

INVOICE PART_NBR_1 PART_DESC_1 PART_NBR_2 PART_DESC_2
12345 215487 AAABBCC 245178 BBAACC
12346 234578 BBCACA
12456 324512 ABABAB
12457 234578 BBCACA 245178 BBAACC


SQL Script:

SELECT T.FCLTY_NO, T.VIN, T.WO_NO, T.WOL_NO, T.WO_OPEN_DT, T.WO_COMPL_DT, T.LAST_SUBM_DT, T.VEH_MILE_QT, T.APPR_CVR_CD,
T.PNC_CD, T.PNC_TX, T.PFP, T.TECH_ID, T.UKEY,
MAX(CASE WHEN T.SEQ_NO = 1 THEN T.SEQOPCD END) OPCD,
MAX(CASE WHEN T.SEQ_NO = 2 THEN T.SEQOPCD END) OPCD1,
MAX(CASE WHEN T.SEQ_NO = 3 THEN T.SEQOPCD END) OPCD2,
MAX(CASE WHEN T.SEQ_NO = 4 THEN T.SEQOPCD END) OPCD3,
MAX(CASE WHEN T.SEQ_NO = 5 THEN T.SEQOPCD END) OPCD4
FROM
(SELECT DISTINCT A.FCLTY_NO, B.SEQ_NO, B.OPER_CD, B.SEQ_NO||B.OPER_CD SEQOPCD, E.DESC_TX OPCD_DESC, A.VIN_PFX_CD||A.VIN_SER_NO VIN, A.WO_NO, A.WOL_NO, C.WO_OPEN_DT, C.WO_COMPL_DT,
C.LAST_SUBM_DT, C.VEH_MILE_QT, A.APPR_CVR_CD, A.PNC_CD, F.PNC_TX, D.PART_NO PFP, H.TECH_ID, A.VIN_PFX_CD||A.VIN_SER_NO||A.WO_NO||A.WOL_NO UKEY
FROM DRBA.SVC_WOL_COMN_T A,
DRBA.SVC_WOL_OPER_T B,
DRBA.SVC_WO_T C,
DRBA.SVC_PRIM_PART_T D,
DRBA.SVC_OPER_T E,
DRBA.VSM_PNC_T F,
USER9.TBL_VDECIDE_VINS G,
DRBA.SVC_TECH_ID_T H
WHERE G.VIN_PFX_CD = A.VIN_PFX_CD
AND G.VIN_SER_NO = A.VIN_SER_NO
AND A.FCLTY_NO = B.FCLTY_NO (+)
AND A.VIN_PFX_CD = B.VIN_PFX_CD (+)
AND A.VIN_SER_NO = B.VIN_SER_NO (+)
AND A.WO_NO = B.WO_NO (+)
AND A.WOL_NO = B.WOL_NO (+)
AND A.FCLTY_NO = C.FCLTY_NO (+)
AND A.VIN_PFX_CD = C.VIN_PFX_CD (+)
AND A.VIN_SER_NO = C.VIN_SER_NO (+)
AND A.WO_NO = C.WO_NO (+)
AND A.FCLTY_NO = D.FCLTY_NO (+)
AND A.VIN_PFX_CD = D.VIN_PFX_CD (+)
AND A.VIN_SER_NO = D.VIN_SER_NO (+)
AND A.WO_NO = D.WO_NO (+)
AND A.WOL_NO = D.WOL_NO (+)
AND A.FCLTY_NO = H.FCLTY_NO (+)
AND A.VIN_PFX_CD = H.VIN_PFX_CD (+)
AND A.VIN_SER_NO = H.VIN_SER_NO (+)
AND A.WO_NO = H.WO_NO (+)
AND A.WOL_NO = H.WOL_NO (+)
AND B.OPER_CD = E.OPER_CD
AND A.PNC_CD = F.PNC_CD
AND C.LAST_SUBM_DT BETWEEN '01APR2015' AND '31AUG2015'
ORDER BY A.FCLTY_NO, A.VIN_PFX_CD||A.VIN_SER_NO, A.WO_NO, A.WOL_NO, B.SEQ_NO) T
GROUP BY T.FCLTY_NO, T.VIN, T.WO_NO, T.WOL_NO, T.WO_OPEN_DT, T.WO_COMPL_DT, T.LAST_SUBM_DT, T.VEH_MILE_QT, T.APPR_CVR_CD,
T.PNC_CD, T.PNC_TX, T.PFP, T.TECH_ID, T.UKEY
ORDER BY T.FCLTY_NO, T.VIN, T.UKEY

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 5 | Registered: November 23, 2016Report This Post
Virtuoso
posted Hide Post
Using FOCUS you need to perform something such as (assuming that a master file exist for SVC_WOL_COMN_T)
TABLE FILE SVC_WOL_COMN_T
SUM PART_NBR PART_DESC
ACROSS INVOICE
WHERE ...
END


Or you can use the SQL pass-through technic with FOCUS.
But you'll need to replace xyz and connection_abc with the proper values.
ENGINE xyz SET DEFAULT_CONNECTION conection_abc
SQL xyz PREPARE SQLOUT FOR
...Put your SQL query here...
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
END
-RUN

TABLE FILE RPT_DATA
SUM PART_NBR PART_DESC
ACROSS INVOICE
WHERE ...
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Thank you, I'll give it a try. Regarding the SQL pass-through, I am not familiar with it, nor are any of my team members; where can I get information on it?

Also, why the Invoice across and not the part number?
 
Posts: 5 | Registered: November 23, 2016Report This Post
Expert
posted Hide Post
FOCUS for Mainframe > Creating Reports > Using SQL to Create Reports.

The ACROSS column is the Dimension, not the Measure:

Print Measure [By/Across] Dimension.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Hi Casaler,
First, welcome to the WebFOCUS forum.
Then, could you update your signature - location, WF version etc.?
As for your question, here is a possible solution:
  
-* File casaler01.fex
-* Create an in-memory master for TBL
EX -LINES 6 EDAPUT MASTER,TBL,C,MEM
FILENAME=TBL, SUFFIX=FIX
SEGNAME=TBL, SEGTYPE=S0
FIELDNAME=INVOICE, ALIAS=INV, FORMAT=I5, ACTUAL=A5,$
FIELDNAME=PART_NBR, ALIAS=PNUM, FORMAT=I6, ACTUAL=A6,$
FIELDNAME=PART_DESC, ALIAS=PDESC, FORMAT=A10, ACTUAL=A10,$
-RUN
-* Logical data file TBL
FILEDEF TBL DISK TBL.FTM
-RUN
-* Put data into physical file TBL
-WRITE TBL 12345215487AAABBCC
-WRITE TBL 12345245178BBAACC
-WRITE TBL 12346234578BBCACA
-WRITE TBL 12456324512ABABAB
-WRITE TBL 12457234578BBCACA
-WRITE TBL 12457245178BBAACC
-RUN
-* Number the parts per invoice
DEFINE FILE TBL
PP/I2=IF INVOICE NE LAST INVOICE THEN 1 ELSE PP + 1;
END
-* Output
TABLE FILE TBL
SUM PNUM PDESC
BY INVOICE ACROSS PP
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Move duplicated records from row view to across column view

Copyright © 1996-2020 Information Builders