I need to concatenate a list of company names separated by a comma. We can have up to 2000 companies to list. Is there another way for me to accomplish this? It appears that there is an & variable limit length of 7000 because if I select 140 records, it works fine. If I select 141 I get an EDA Server Crash error.

-SET &ctr = 1 ;
-SET &&NEWCOMPANYNAME = '';

TABLE FILE DIRECTDB_COMPANYDIMENSION
BY COMPANYID NOPRINT
BY COMPANYNAME
ON TABLE HOLD AS HCOMPANY
END

-SET &companyCtr = 140;

-RUN

-CompanyLoop
-READ HCOMPANY &COMPANYNAME.A50.
-SET &&NEWCOMPANYNAME = &&NEWCOMPANYNAME || ',' || &COMPANYNAME ;
-SET &ctr = &ctr + 1 ;
-IF &ctr GT &companyCtr GOTO endCompany ;
-GOTO CompanyLoop
-endCompany
-RUN

-TYPE &&NEWCOMPANYNAME;

This message has been edited. Last edited by: Kerry,

Laurie,

Is there a reason why you need a single row that is potentially going to be 100,000 bytes long?

I have great difficulty seeing what you could possibly do with it, except have problems in any
system or application that you were sending it to.

A 100 thousand byte string can not be processed in any DB unless it's a CLOB,
and WebFocus can not handle CLOBs.

Currently we list all companies that could be used in the query criteria for our reports. It will very seldom reach that limit but it has the potential to do so. I will check to see if I can just set the limit at 140. Thanks.

Laurie,

The alternative is to show multiple rows basically as you are each containing a maximum of 140 companies.

ON TABLE HOLD AS HCOMPANY
END
-RUN
-SET &MAXCOS=&LINES;
-SET &OLOOP = INT(((&MAXCOS/140)+1));
-SET &companyCtr = 140;

-REPEAT OLOOP &OLLOP TIMES
-SET &&NEWCOMPANYNAME='';
-SET &ctr =0;
-CompanyLoop
-READ HCOMPANY &COMPANYNAME.A50.
-SET &&NEWCOMPANYNAME = &&NEWCOMPANYNAME || ',' || &COMPANYNAME ;
-SET &ctr = &ctr + 1 ;
-IF &ctr GT &companyCtr GOTO endCompany ;
-GOTO CompanyLoop
-endCompany
-TYPE &&NEWCOMPANYNAME
-OLOOP
-RUN


If you want to keep all &&NEWCOMPANYNAMEs add an extra variable as a counter on OLOOP
so that they become &&NEWCOMPANYNAME1 &&NEWCOMPANYNAME2 etc.

I could try create a couple more loops and sequentially rename them based on the count and then concatenate all of the & variables after it is finished. Rather kludgy but as a last resort I will probably try it.

Do you have any idea why this syntax doesn't work. It concatenates exactly the way it is supposed to but truncates the result at 1220 characters. As the next new company is concatenated, it drops off the first one to add the next one. Why won't this use the full A7000 available? It can't get any more straight forward than this.

DEFINE FILE DIRECTDB_COMPANYDIMENSION
DUMY/A1 = '' ;
END

TABLE FILE DIRECTDB_COMPANYDIMENSION
PRINT DUMY
COMPUTE D_NEWCOMPANY/A7000 = IF DUMY EQ LAST DUMY THEN COMPANYNAME || ',' | SUBSTR(7000, LAST D_NEWCOMPANY, 1, 7000, 7000, 'A6000') ELSE COMPANYNAME ;
ON TABLE HOLD AS HCOMPANY
END

quote:

A7000

Cause Maximum ALPHA in WebFocus is A4096

John,
It does not matter how you massage the code A7000 is not valid.

The rules are

alphanumeric in a Focus DB maximum          3968 characters
alphanumeric in a XFocus DB maximum         4096 characters
alphanumeric in a Fixed format file maximum 4095 characters

If your query (where you will use the list of companies) is in FOCUS, why not create a file with one comapny on each line? Then it will not matter how many companies you have, and you can use the IN FILE syntax.

Diptesh,
That is not going to work if you potentially have a file of upto a 100K
because of the 16K limit for a file used in WHERE and the 32K limit using IF

Laurie,

A way to create the string that you require in a flat file is to pre allocate the file
as a fixed length file.
This will write the file without the normal CR/LF.

And effectivly create a single row of your potentially needed 100,000 bytes with no problem
but there are limitations on what you can do with that data record in WebFocus.

In your case the code would be

FILEDEF HCOMPANY DISK HCOMPANY.FTM (LRECL 51 RECFM F
-RUN
TABLE FILE DIRECTDB_COMPANYDIMENSION
PRINT
COMPUTE D_NEWCOMPANY/A51 = COMPANYNAME | ',';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS HCOMPANY
END
-RUN
-SET &MAXLRECL=(&LINES * 51) + &LINES;
-READ HCOMPANY &FULLRECL.A&MAXLRECL.EVAL.

The output file will appear as a single string. However it does add a leading space to each D_NEWCOMPANY.

The -READ will create a variable upto 32740 bytes (WF 7.6.7)

This is a built in WebFocus Limitation.

Any number between 32740 and 32767 (32K -1) crashes the server
and any number above 32768 (32K) returns an invalid format error.

There is NO WAY that you can concat to any total variable length greater than 32767 (32K -1)
it will crash the server.

I still have to ask though, Why do you need such a potentially long string variable?

It can not be used for anything in WebFocus and the only way to print it would be landscape
on a toilet roll.

Please consider this closed. I am unable to produce the output that is necessary for our reports so I am going to generate the string with a SQL stored procedure and pass that to a fex to display on the report. I used a substring function to remove the trailing blanks but then when I use -READ for that field, it adds the empty characters back into the field. Thanks for your help.