Focal Point
[SOLVED] The Loop of Death w/Concatention !!Guru Needed Please!!

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3161020902

January 30, 2007, 09:43 AM
claymeone
[SOLVED] The Loop of Death w/Concatention !!Guru Needed Please!!
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
January 30, 2007, 10:09 AM
mgrackin
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
February 07, 2007, 05:21 PM
claymeone
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
February 08, 2007, 09:54 AM
Francis Mariani
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
February 08, 2007, 10:14 AM
mgrackin
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
February 08, 2007, 10:47 AM
mgrackin
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
February 08, 2007, 10:53 AM
mgrackin
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
February 08, 2007, 11:01 AM
claymeone
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
February 08, 2007, 11:04 AM
mgrackin
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
February 13, 2007, 09:23 AM
mgrackin
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
May 08, 2009, 09:21 AM
Mike Johnson
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
May 08, 2009, 09:58 AM
Francis Mariani
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
May 08, 2009, 09:58 AM
GinnyJakes
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
May 08, 2009, 10:01 AM
<JG>
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

May 08, 2009, 10:05 AM
Mike Johnson
Yes, it could be that long. I thought the limit was extended to 32k.
May 08, 2009, 10:07 AM
Mike Johnson
Thanks, That worked. Can you explain why this Varabile format is?
May 08, 2009, 10:09 AM
<JG>
Variable format was introduced to WebFOCUS for compatibility with relational table varchar2 which can be upto 32k
May 08, 2009, 10:10 AM
Mike Johnson
Well, I jumped the GUN. Using V did not work.
May 08, 2009, 10:15 AM
Mike Johnson
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;

May 08, 2009, 11:26 AM
<JG>
Mike it's variable so SUBSTR has problems

TRY using SUBSTV

Character Functions for AnV Fields
May 08, 2009, 11:32 AM
Mike Johnson
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.
May 08, 2009, 02:21 PM
j.gross
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
May 12, 2009, 02:04 PM
Mike Johnson
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?????
May 12, 2009, 03:19 PM
j.gross
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
May 12, 2009, 03:25 PM
Mike Johnson
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:
May 12, 2009, 03:51 PM
j.gross
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
June 04, 2009, 04:30 PM
Kerry
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.
June 05, 2009, 07:46 AM
Mike Johnson
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.