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

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

claymeone

Member

posted

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, 2005

Ignored post by claymeone posted

Francis Mariani

Expert

posted

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, 2005

Ignored post by Francis Mariani posted

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

claymeone

Member

posted

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, 2005

Ignored post by claymeone posted

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

Virtuoso

posted

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, 2003

Ignored post by mgrackin posted

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted

Francis Mariani

Expert

posted

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, 2005

Ignored post by Francis Mariani posted

GinnyJakes

Expert

posted

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, 2006

Ignored post by GinnyJakes posted

<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

Gold member

posted

Yes, it could be that long. I thought the limit was extended to 32k.

Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005

Ignored post by Mike Johnson posted

Gold member

posted

Thanks, That worked. Can you explain why this Varabile format is?

Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005

Ignored post by Mike Johnson posted

<JG>

posted

Variable format was introduced to WebFOCUS for compatibility with relational table varchar2 which can be upto 32k

Gold member

posted

Well, I jumped the GUN. Using V did not work.

Posts: 84 | Location: Tallahassee, Fl. | Registered: December 02, 2005

Ignored post by Mike Johnson posted

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted

<JG>

posted

Mike it's variable so SUBSTR has problems

TRY using SUBSTV

Character Functions for AnV Fields

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted

j.gross

Virtuoso

posted

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, 2005

Ignored post by j.gross posted

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted

j.gross

Virtuoso

posted

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, 2005

Ignored post by j.gross posted

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted

j.gross

Virtuoso

posted

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, 2005

Ignored post by j.gross posted

Kerry

Expert

posted

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, 2004

Ignored post by Kerry posted

Gold member

posted

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, 2005

Ignored post by Mike Johnson posted