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     position and substring question

Read-Only Read-Only Topic
Go
Search
Notify
Tools
position and substring question
 Login/Join
 
Platinum Member
posted
I have a report in which I list discriptions (decode) based on a code I retrieve from a database. An example follows:
 FRAUD0DESC/A37= DECODE rsp0errortxt(1001 '1001 - Missing Record Type'
                                  2001 '2001 - Invalid Record Type'
                                  1003 '1003 - Missing Acquirer ID'
                                  2003 '2003 - Invalid Acquirer ID'


I have found out that their may be several discriptions to display and when that situation exist my field rsp0errortxt will be retrieved as i.e. '200120022003'...

could I get some help as to how to parse this out so that I migth retrieve the correct decoded description...also this might contain alot of 4 digit codes.

Thanks




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
Virtuoso
posted Hide Post
you can use edit to retrieve the characters by fours from the field like:

CODE1/A4=EDIT(rsp0errortxt,'9999');
CODE2/A4=EDIT(rsp0errortxt,'$$$$9999');

There are other functions to do the same thing but edit is the easiest.

The problem will be, if there are multiple values in that field, are you only concerned with the description for the first field, or for descriptions for all the fields? Will each description have to appear as its own record? Lots of additional things to think about...


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Thanks Darin,

Your right their is alot of additional things to consider with this...each disc has to appear as it's own record...I solved this problem with the following code... I had to loop thru to find each occurence because 1 record might have 3 occurences...I retrieved all info needed (specafically all occurences of the error description...put the info in a temp file...re-tabled the tempfile assigning my decode values to the ERRORCD field
 DEFINE FILE DISRESPFIL10
RECCNTR/I2 = IF rsp0rectype EQ LAST rsp0rectype AND
                rsp0disacqid EQ LAST rsp0disacqid AND
                rsp0dismchnum EQ LAST rsp0dismchnum AND
                rsp0dbaname EQ LAST rsp0dbaname THEN RECCNTR + 1
             ELSE 1;
ERRORCD/A4 = SUBSTR(200, rsp0errortxt, (RECCNTR * 4 -3), (RECCNTR * 4), 4, 'A4');
ERRORCT10/I4 = &TOTERR10_CNT;
END
-*
SET COMPOUND='NOBREAK'
-*SET PRINTPLUS=ON
APP HOLD emr_work
-SET &OCCURENCE = 1;
FILEDEF TEMPFFILE1 DISK emr_work/TEMPFFILE1.ftm
-RUN
-REPEATTF
TABLE FILE DISRESPFIL10
PRINT
rsp0errorct      AS 'Nbr of Errors'
ERRORCD          AS 'ERROR Code - Desc'


BY rsp0rectype   AS 'Record Type'
BY rsp0disacqid  AS 'Acquirer ID'
BY rsp0dismchnum AS 'Discover Acct Nbr'
BY rsp0dbaname   AS 'Merchant DBA'
WHERE RECCNTR EQ &OCCURENCE
ON TABLE HOLD AS TEMPFFILE1 FORMAT ALPHA
END
-SET &OCCURENCE = &OCCURENCE + 1;
FILEDEF TEMPFFILE1 DISK emr_work/TEMPFFILE1.ftm (APPEND
-RUN
-IF &OCCURENCE LE 50 GOTO REPEATTF;

-RUN



DEFINE FILE TEMPFFILE
ERRORCT10/I5 = &TOTERR10_CNT;
FRAUD0DESC/A37=DECODE ERRORCD(1001 '1001 - Missing Record Type'
                                  2001 '2001 - Invalid Record Type'
                                  1003 '1003 - Missing Acquirer ID'
                                  2003 '2003 - Invalid Acquirer ID'
                                  2004 '2004 - Invalid ISO ID'
                                  1005 '1005 - Missing Merchant Type'
                                  2005 '2005 - Invalid Merchant Type'
                                  1006 '1006 - Missing Headquater Acct Num'
                                  2006 '2006 - Invalid Headquater Acct Num'
                                  2007 '2007 - Invalid Disc Network Mrch Num'
                                  1008 '1008 - Missing Merchant DBA Name'
                                  1009 '1009 - Missing Mrch DBA Addr Line 1'
                                  1011 '1011 - Missing Merchant DBA City'
                                  1012 '1012 - Missing Merchant DBA State'
                                  2012 '2012 - Invalid Merchant DBA State'
                                  1013 '1013 - Missing Mrch DBA Postal Code'
                                  2013 '2013 - Invalid Mrch DBA Postal Code'
                                  1014 '1014 - Missing Merchant DBA Ctry Code'
                                  2014 '2014 - Invalid Merchant DBA Ctry Code'
                                  1015 '2003 - Missing Mrch DBA Phone Num'
                                  2015 '2015 - Invalid Mrch DBA Phone Num'
                                  1016 '1016 - Missing Bus Contact First Nme'
                                  1017 '1017 - Missing Bus Contact Last Nme'
                                  1018 '1018 - Missing Bus Prncpl Fname'
                                  1019 '1019 - Missing Bus Prncpl Lname'
                                  1020 '1020 - Missing Bus Prncpl Title'
                                  1021 '1021 - Missing Bus Prncpl Hme Adrl1'
                                  1023 '1023 - Missing Bus Prncpl Hme City'
                                  1024 '1024 - Missing Bus Prncpl Hme ST'
                                  2024 '2024 - Invalid Bus Prncpl Hme ST'
          1025 '2003 - Missing Bus Prncpl Hme Zip'
          2025 '2025 - Invalid Bus Prncpl Hme Zip'
          1026 '1026 - Missing Bus Prncpl Hme CTRY CD'
          2026 '2026 - INVL Bus Prncpl Hme CTRY CD'
          1027 '1027 - Missing Bus Prncpl SSN'
          2027 '2027 - Invalid Bus Prncpl SSN'
          1028 '1028 - Missing Tax ID Number'
          2028 '2028 - INVALID Tax ID Number'
          1029 '1029 - Missing Legal Bus Name'
          1030 '1030 - Missing Legal Bus ADR L1'
          1033 '1033 - Missing Legal Bus State'
          2033 '2033 - Invalid Legal Bus State'
          1034 '1034 - Missing Legal Bus Zip'
          2034 '2034 - Invaild Legal Bus Zip'
          1035 '1035 - Missing Legal Bus Ctry Code'
          2035 '2035 - Invalid Legal Bus Ctry Code'
          1036 '1036 - Missing Mrch Category Code'
          2036 '2036 - Invalid Mrch Category Code'
          1037 '1037 - Missing Bus Type Descript'
          2037 '2037 - Invalid Bus Type Descript'
          1038 '1038 - Missing Mrch Status Date'
          2038 '2038 - Invalid Mrch Status Date'
          1039 '1039 - Missing Mrch Status'
          2039 '2039 - Invalid mrch Status'
          1993 '1993 - Missing Record Count'
          2993 '2993 - Invalid Record Count'
          3001 '3001 - Missing Trailer Record'
          3199 '3199 - Incorrect Record Format');
END
-*
SET COMPOUND='NOBREAK'
-*SET PRINTPLUS=ON
TABLE FILE TEMPFFILE
PRINT
rsp0errorct      AS 'Nbr of Errors'
FRAUD0DESC       AS 'ERROR Code - Desc'
AND COMPUTE SEQ/I1 = IF LAST SEQ EQ 1 THEN 0 ELSE 1; NOPRINT

BY rsp0rectype   AS 'Record Type'
BY rsp0disacqid  AS 'Acquirer ID'
BY rsp0dismchnum AS 'Discover Acct Nbr'
BY rsp0dbaname   AS 'Merchant DBA'
BY rsp0errorct   AS 'Nbr OF Errors'
-*
ON rsp0rectype SUBFOOT
" "
"Total Type 10:<ERRORCT10"


ON rsp0disacqid SKIP-LINE
ON TABLE PCHOLD FORMAT PDF
-*ON TABLE HOLD AS HOLDNAME5 FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=DATA, BACKCOLOR=RGB(255 255 255),WHEN=SEQ EQ 1,$
TYPE=DATA, BACKCOLOR=RGB(231 227 231),WHEN=SEQ EQ 0,$
TYPE=REPORT,SIZE=9,FONT='ARIAL',ORIENTATION=LANDSCAPE,SQUEEZE=ON,GRID=OFF,$
TYPE=FOOTING, JUSTIFY=CENTER,$
TYPE=SUBFOOT,LINE=1,OBJECT=FIELD,ITEM=2,POSITION=rsp0disacqid,JUSTIFY=LEFT,$
TYPE=SUBFOOT,LINE=1,OBJECT=FIELD,ITEM=5,JUSTIFY=LEFT,$
TYPE=TITLE,STYLE=BOLD,$
ENDSTYLE
END 




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
<Dane>
posted
If you are going to have this many values or more, I would suggest that you create a FOCUS database and do a JOIN instead of using the DECODE file. IT's faster, stored in one place and easy to maintain.
 
Report This Post
Platinum Member
posted Hide Post
Dane,

My attempt did not work...and of course I'm being told that this MUST be done today...the problem is that the information comes in like this: errorct: 4 errortxt: 2001200220032004

and I need to display each errortxt on it's own line ...could you please show me an example of your suggestion.

Thanks,




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Darin Lee:
you can use edit to retrieve the characters by fours from the field like:

CODE1/A4=EDIT(rsp0errortxt,'9999');
CODE2/A4=EDIT(rsp0errortxt,'$$$$9999');

There are other functions to do the same thing but edit is the easiest.

The problem will be, if there are multiple values in that field, are you only concerned with the description for the first field, or for descriptions for all the fields? Will each description have to appear as its own record? Lots of additional things to think about...


Darin My attempt did not work and I believe this is causing my agents to crash. In answer to your question. I will not only need the description for the first code...but for each code. the report will look something like this
 
rec typ  acq_id error_count error_desc
10        1234        4     2001 - desc
                            2002 - desc
                            2003 - desc
                            2004 - desc



any suggestion would greatly be appreciated.




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
Expert
posted Hide Post
I'm not sure you can get this done today, but to build on what Darin told you (and I can't figure out what you are doing in your example), you could use something called the alternate master technique. This involves PRINTing all of your error codes after you have substringed them across the page by your static info for each row. Then hold it. Then write a master for the hold file that OCCURS the codes by how many you have. Then filedef the hold file to the alternate master and now you can print code code decription by rectype by acq_id by error_count. This is not easy to explain without writing a lot of code and words to explain it.

Let me know if you are interested in pursuing this further.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
Ginny and (everyone else)..I did get my previously posted code to work correctly. The problem turned out to be that I was specifing a location for my temp files while using the EDA tempspace when creating my compound pdf report...thus causing EDA to become confused...thus crashing agents. All is well at this point...

Thanks,
Timothy




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
Virtuoso
posted Hide Post
Glad to hear you got it working. My next idea was to use the OVER syntax where all fields are actually the same record, but by using OVER as in
PRINT
ERROR1 OVER
ERROR2 OVER
ERROR3 OVER
ERROR4
BY REC_TYP
BY ACQ_ID
BY ERROR_COUNT

it makes it LOOK like there are multiple records being prodcued from a single record. I call this my MacGuyver shortcut. (MacGuyver technique is a WF technique used to create multiple records from a single record - which is exactly what you are trying to do.) My way doesn't actually create multiple records, but it appears to be be like that on the report.

Good work!


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report 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     position and substring question

Copyright © 1996-2020 Information Builders