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     [SOLVED] The Loop of Death w/Concatention !!Guru Needed Please!!
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] The Loop of Death w/Concatention !!Guru Needed Please!!
 Login/Join
 
Member
posted Hide Post
Well, not quite. I tried all the advice of everyone but the SUBSTR function will still not exceed about approximately 270+ characters. I removed the V in the master file; I changed the values based on the amount of expected characters etc. I kind of gave up.


Clay Williams
WebFOCUS 7.1.4
 
Posts: 26 | Location: Frederick Maryland | Registered: October 07, 2005Report This Post
Virtuoso
posted Hide Post
Clay,

The only other suggestion I can think of is to make sure you are telling WebFOCUS to convert LONGCHARS properly to ALPHA fieldtypes. I assume you have a relational database on the backend and therefore need to make sure WebFOCUS is handling large character strings properly.

If this does not help, open a case with IBI.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Member
posted Hide Post
quote:
Originally posted by claymeone:
Francis,

Is there size limitation to using the SUBSTR function? I used the same concept as the state for some other larger field sizes. And it seems like when the string gets too large it starts to chop of names and repeats only pieces of the information. I am currently using this for the name field: SDO_LIST/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SDO_LIST, 1, 250, 450, 'A450')



The length for the one with the problem is about 300 characters. I've even tried to increase the string end value but to no avail. Can you provide any guidance on this problem.


Clay Williams
WebFOCUS 7.1.4
 
Posts: 26 | Location: Frederick Maryland | Registered: October 07, 2005Report This Post
Expert
posted Hide Post
The manual does not state size limitations for the SUBSTR function, so I'm sorry I can't help you with that.

There appears to be several functions for AnV fields (see "Character Functions for AnV Fields"), one of them is SUBSTV - "Extracting a Variable Length Substring" which may be of help.


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
I have never had issues with the SUBSTR function although I do not recall using a field as large as the field you have nor using a field with an AnV format. I was not aware that there were specific functions for AnV format fields. My guess is that the AnV format of the field is what is causing the issue and the suggestion by Francis to try the SUBSTV function may fix the issue.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
Clay,

I did more testing and was able to duplicate the issue.

Remove the parenthesis from around the (' ' | SDL).

Code your statement like this:

SHORTDSC/A655=SUBSTR(2000,SOL_DESC,1,400,400,'A400') || '' | SOL_SUM;

NOT LIKE THIS:

SHORTDSC/A655=SUBSTR(2000,SOL_DESC,1,400,400,'A400') || ('' | SOL_SUM);

In the above examples SOL_DESC and SOL_SUM are A2000V and A254V format fields respectively. The MFD describes a relational table in Oracle 9.2.

I hope this helps.

I would also open a case with IBI to look into this.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
Just a little more ifo on this. It does not matter what the format of the fields are in the MFD (Annn or AnnV) it will work WITHOUT the parenthesis ( ). It will NOT work if the parenthesis are in the statement. Very interesting.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Member
posted Hide Post
So I should remove the ()? Not sure
SSDO_LIT/A800 = IF PROGRAMNAME EQ LAST PROGRAMNAME THEN SUBSTR(800, SSDO_LIT, 1, 250, 450, 'A450') || ('' | SSDO) ELSE SSDO;


Clay Williams
WebFOCUS 7.1.4
 
Posts: 26 | Location: Frederick Maryland | Registered: October 07, 2005Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by mgrackin:
Code your statement like this:

SHORTDSC/A655=SUBSTR(2000,SOL_DESC,1,400,400,'A400') || '' | SOL_SUM;

NOT LIKE THIS:

SHORTDSC/A655=SUBSTR(2000,SOL_DESC,1,400,400,'A400') || ('' | SOL_SUM);


Clay,

See my examples above to see which ( ) set to remove. I was able to duplicate the error so your code should work if you remove the correct pair.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
Clay,

Is the issue you were having with the SUBSTR resolved by removing the ( )?

Just curious since this one was tricky.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
I have a similar probelm and since this tread was not closed I decided to reply.

I have the following fields:

RMRKS_TXT_FLD/A254
RMRKS_SN/P4
RMRKS_ID/A28
RMRKS_T/A4

I have the following Define

DEFINE FILE TSMEM298
CNTR/I9 WITH RMRKS_SN = CNTR + 1;
KEY1/A68 = RMRKS_ID||RMRKS_T;
ENDP/I8 = ARGLEN(254, RMRKS_TXT_FLD, ENDP);
LREN/I8 = IF (CNTR EQ 1) OR (KEY1 NE LAST KEY1) THEN ENDP ELSE ENDP + LAST LREN;
NEW_RMK1/A254 = RMRKS_TXT_FLD;
-*RMRK/A4000 = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE (SUBSTR(4000, RMRK, 1, LREN, LREN, 'A3746')) || NEW_RMK1;
RMRK/A25146 = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE SUBSTR(25146, RMRK, 1, LREN, LREN, 'A24891') || NEW_RMK1;
DWR_DATEYX/P8YYMD = DWR_DT;
DWR_DATEY/MDYY = DWR_DATEYX;
DWR_DATE1/A10 = EDIT(DWR_DATEY,'99/99/9999');
-*DATETY/P8=AUTH_DT;
DATEXX/P8YYMD=AUTH_DT;
AUD_DATE/MDYY=DATEXX;
END  


This line works but I can have upto 99 records at A254 so that's a limit of A25146.

RMRK/A4000 = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE (SUBSTR(4000, RMRK, 1, LREN, LREN, 'A3746')) || NEW_RMK1;

this line does not work, it leaves out some the text.
RMRK/A25146 = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE SUBSTR(25146, RMRK, 1, LREN, LREN, 'A24891') || NEW_RMK1;

Thanks
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Expert
posted Hide Post
There are limits to what WebFOCUS can do, and there is a limit to the length of a field, probably 4000 characters.

Are you trying to display a 25146 character long field on a report?


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
Expert
posted Hide Post
Use a V or variable format.


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
<JG>
posted
quote:
A25146

Sorry that's just not valid.

quote:
The alphanumeric USAGE type is A. The format of the length specification is n, where n is the maximum number of characters in the field. You can have up to 3968 bytes in an alphanumeric field in a FOCUS file segment, and up to 4096 bytes in an XFOCUS file segment. You can have up to 4095 bytes in a fixed-format sequential data source. You may define the length in the Master File, a DEFINE FILE command, or a COMPUTE command
 
Report This Post
Gold member
posted Hide Post
Yes, it could be that long. I thought the limit was extended to 32k.
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Gold member
posted Hide Post
Thanks, That worked. Can you explain why this Varabile format is?
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
<JG>
posted
Variable format was introduced to WebFOCUS for compatibility with relational table varchar2 which can be upto 32k
 
Report This Post
Gold member
posted Hide Post
Well, I jumped the GUN. Using V did not work.
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Gold member
posted Hide Post
What is the format usage. I've looking for it to comfirm my usage.

 RMRK/A25146V = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE SUBSTR(25146, RMRK, 1, LREN, LREN, 'A24892V') || NEW_RMK1;
 
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
<JG>
posted
Mike it's variable so SUBSTR has problems

TRY using SUBSTV

Character Functions for AnV Fields
 
Report This Post
Gold member
posted Hide Post
Thanks, I'm working on it. But so far still having issues. I've called to other duties for the rest of the day so I must postpone this until Monday. Thanks for all the help.
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Virtuoso
posted Hide Post
Now that you are using varchar, try it without the SUBSTR:
RMRK/A25146V = IF KEY1 NE LAST KEY1 
 THEN         NEW_RMK1 
 ELSE RMRK || NEW_RMK1;


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
J.Gross I tried your idea with the following problem.

Can someone explain why the following code works but the second example does not.

example one:
DEFINE FILE TSMEM298
CNTR/I9 WITH RMRKS_SN = CNTR + 1;
KEY1/A68 = RMRKS_ID||RMRKS_T;
RMRKSV/A254 = RMRKS_TXT_FLD;
ENDP/I8 = LENV(RMRKSV, ENDP);
LREN/I8 = IF (CNTR EQ 1) OR (KEY1 NE LAST KEY1) THEN ENDP ELSE ENDP + LAST LREN;
NEW_RMK1/A254 = RMRKS_TXT_FLD;
NEWRMK/A16382V = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE NEWRMK||NEW_RMK1;
DWR_DATEYX/P8YYMD = DWR_DT;
DWR_DATEY/MDYY = DWR_DATEYX;
DWR_DATE1/A10 = EDIT(DWR_DATEY,'99/99/9999');
DATEXX/P8YYMD=AUTH_DT;
AUD_DATE/MDYY=DATEXX;
END
TABLE FILE TSMEM298
SUM
CD_DESC AS 'Remarks Type:'
NEWRMK
BY 'KEY1' NOPRINT
BY HIGHEST 1 RMRKS_SN NOPRINT
WHERE ( CONT_ID EQ '&ContID.Enter ContID.' );
WHERE ( CMS_UID EQ '&UserID.Enter UserID.' );
WHERE ( DWR_DATEY EQ '&DWRDate.Enter DWR Date.' );
END  


example two:
 DEFINE FILE TSMEM298
CNTR/I9 WITH RMRKS_SN = CNTR + 1;
KEY1/A68 = RMRKS_ID||RMRKS_T;
RMRKSV/A254 = RMRKS_TXT_FLD;
ENDP/I8 = LENV(RMRKSV, ENDP);
LREN/I8 = IF (CNTR EQ 1) OR (KEY1 NE LAST KEY1) THEN ENDP ELSE ENDP + LAST LREN;
NEW_RMK1/A254 = RMRKS_TXT_FLD;
NEWRMK/A16383V = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE NEWRMK||NEW_RMK1;
DWR_DATEYX/P8YYMD = DWR_DT;
DWR_DATEY/MDYY = DWR_DATEYX;
DWR_DATE1/A10 = EDIT(DWR_DATEY,'99/99/9999');
DATEXX/P8YYMD=AUTH_DT;
AUD_DATE/MDYY=DATEXX;
END
TABLE FILE TSMEM298
SUM
CD_DESC AS 'Remarks Type:'
NEWRMK
BY 'KEY1' NOPRINT
BY HIGHEST 1 RMRKS_SN NOPRINT
WHERE ( CONT_ID EQ '&ContID.Enter ContID.' );
WHERE ( CMS_UID EQ '&UserID.Enter UserID.' );
WHERE ( DWR_DATEY EQ '&DWRDate.Enter DWR Date.' ); 


The Only difference in the two examples is the following lines.

Example One:
NEWRMK/A16382V = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE NEWRMK||NEW_RMK1;
  


Example Two:
NEWRMK/A16383V = IF KEY1 NE LAST KEY1 THEN NEW_RMK1 ELSE NEWRMK||NEW_RMK1;
  

Please can someone explain why?????
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Virtuoso
posted Hide Post
When you increae n (of AnV) above 16382, what's your error message?

There's an overall limit (jointly, for all the real and defined fields in a segment), after which you get

(FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
Sorry should have included this. The error message is below.

Unknown error occurred.
Agent on reporting server WFWINP1 may have crashed.
Please investigate reporting server log.

ERROR:
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
Virtuoso
posted Hide Post
That's a client-side message (no response from server).

EDAPRINT (written by the server) may reveal more.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
Hi Mike,

Has this issue been resolved on your side?

If not, please contact Information Builders' Customer Support Services and open a case for assistance, since it's crashing the agent on the reporting server. To open a case, you may either call at 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Gold member
posted Hide Post
Sorry, been busy.

Yes and No. We opened a case with IBI and we are still waiting for a Solution.

One suggestion IBI offered was to breakup the remarks into 4096 blocks. I have used this option until we get a response from IBI.
 
Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] The Loop of Death w/Concatention !!Guru Needed Please!!

Copyright © 1996-2020 Information Builders