Hi, Im using Webfocus 8203 and Im using a TABLE FILE to re...

[Ricardo Lara]

Hi,

Im using Webfocus 8203 and Im using a TABLE FILE to read all the records in a flat file and build a unique string with the Id and Amount of each record to use later with DECODE. The string variable is defined as AnV because it didnt let me do it using a regular string, and it works fine if I send the output to the browser, but if I set the HOLD as FORMAT ALPHA and then -READ it, it adds some funny numbers at the beginning of the string that I presume is the string length, but I dont need them there.

This is the code Im testing:

DEFINE FILE ff_sales_2

NEW_TRGT_ID/A11 = PTOA(DIVISION_ID,(P11),A11);

NEW_TRGT_TMP/A50 = TRIM(B, NEW_TRGT_ID, 11, , 1, A11) | || TRIM(B, FTOA(SALES_TARGET, (D12c), A20), 20, , 1, A20) || ;

NEW_TRGT_STR/A2000V = NEW_TRGT_STR | | NEW_TRGT_TMP ;

END

TABLE FILE ff_sales_2

SUM

MAX.NEW_TRGT_STR

ON TABLE HOLD AS USTO_NEW_TRGT FORMAT FOCUS

END

-RUN

TABLE FILE USTO_NEW_TRGT

PRINT NEW_TRGT_STR

END

-RUN

 

 

Output in the browser:

 

 

NEW_TRGT_STR

1 23791 2 26901 3 23930 4 25614 5 21689 6 27173 7 24449

 

 

But if I change the second HOLD to:

 

 

TABLE FILE USTO_NEW_TRGT

PRINT NEW_TRGT_STR

ON TABLE HOLD AS USTO_NEW_TRGT FORMAT ALPHA

END

-RUN

-READ USTO_NEW_TRGT &NEW_TRGT_STR.A2000

-TYPE &NEW_TRGT_STR

 

 

Output is :

 

 

TABLE FILE USTO_NEW_TRGT

PRINT NEW_TRGT_STR

ON TABLE HOLD AS USTO_NEW_TRGT FORMAT ALPHA

END

-RUN

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1

-READ USTO_NEW_TRGT &NEW_TRGT_STR.A2000

-TYPE &NEW_TRGT_STR

000358 1 23791 2 26901 3 23930 4 25614 5 21689 6 27173 7 24449

How can I get rid off the 000358

I did some editing for security reasons and to make it understandable.

To read the ALPHA hold I tried using AnV format for the variable but I got an error message.

Thanks!

Ricardo

[Dirk Kuerbig]

ricardo.lara:

 

ON TABLE HOLD AS USTO_NEW_TRGT FORMAT ALPHA

END

-RUN

-READ USTO_NEW_TRGT &NEW_TRGT_STR.A2000

-TYPE &NEW_TRGT_STR

 

 

Hello Ricardo.

You are absolutely right in your observations that Varchar columns save the length of the column internally, and if you use -READ which means you just treat the ALPHA output as a text file, you will pick up this length.

There is a newer alternative to -READ which is

-READFILE

This command converts every column you reference in the TABLE FILE output that gets stored in the HOLD file into a variable with the same name as the field itself.

You should initialize the variables you retrieve with a default (hidden) -DEFAULTH value to avoid prompting for the parameter.

Here is the syntax to use instead of -READ

-* not required

-*-READ USTO_NEW_TRGT &NEW_TRGT_STR.A2000

-* Initialize variable from HOLD file

-DEFAULTH &NEW_TRGT_STR= ';

-* Translate every report column of HOLD file into WebFOCUS variable

-READFILE USTO_NEW_TRGT

-TYPE &NEW_TRGT_STR

A few more notes, and a simplified example

TABLE FILE EMPDATA

SUM COMPUTE SALARY_NEW/D20=SALARY;

ON TABLE HOLD AS T_SALARY FORMAT ALPHA

ON TABLE SET HOLDLIST PRINTONLY

END

-RUN

-* Initialize variable from HOLD file

-DEFAULTH &SALARY_NEW= ';

-* Translate every report column of HOLD file into WebFOCUS variable

-READFILE T_SALARY

-TYPE Salary is &SALARY_NEW

ON TABLE SET HOLDLIST PRINTONLY

> ensures that only the fields you print out and not supporting fields (in this case SALARY) will make it into the HOLD file

-DEFAULTH &SALARY_NEW= ';

> Initialize the variable or variables you want to retrieve through a TABLE FILE statement

The beauty of -READFILE is that you are not limited to 1 variable + you do not have to hardwire the length of fields to read from the HOLD file.

Hope this helps

Good luck

Dirk

[David Beagan]

Maybe this approach is easier

TABLE FILE ggsales

PRINT SEQ_NO

COMPUTE T1/A2=' ''';

DOLLAR

COMPUTE T2/A1='''';

ON TABLE HOLD AS USTO_NEW_TRGT FORMAT ALPHA

END

-RUN

-READ USTO_NEW_TRGT &NEW_TRGT_STR.A2000.

-SET &NEW_TRGT_STR = SQUEEZ(&NEW_TRGT_STR.LENGTH, &NEW_TRGT_STR, 'A&NEW_TRGT_STR.LENGTH');

-SET &NEW_TRGT_STR = REPLACE(&NEW_TRGT_STR, ' '' ', ' ''');

-TYPE &|NEW_TRGT_STR = &NEW_TRGT_STR

 

Gives the result

&NEW_TRGT_STR = 1 '20805' 2 '20748' 3 '20376' 4 '20028' 5 '19905' 6 '19470' ...

[Ricardo Lara]

Thanks Dirk, it solved my problem.

Regards,

Ricardo

[Ricardo Lara]

Thanks for your response David,

I tried to replicate your example but Im getting A required parameter is missing

Detail:

&NEW_TRGT_STR

I tried increasing the variable length but gave me the same error.

Thanks again,

Ricardo

[David Beagan]

Must be some 8.2.03 issues. Good that Dirks answer fixed if for you.

[David Beagan]

If youre doing DECODEs maybe this would be useful. You can DECODE based on value pairs from a file (or HOLD file). Quotes can be optional unless you have embedded space like New York.

FILEDEF values DISK values.txt

-RUN

-WRITE values CA California

-WRITE values FL Florida

-WRITE values GA Georgia

-WRITE values IL Illinois

-WRITE values MA Massachusetts

-WRITE values MO Missouri

-WRITE values NY 'New York'

-WRITE values TN Tennessee

-WRITE values TX Texas

-WRITE values WA 12345

 

DEFINE FILE GGSALES

State/A15 = DECODE ST (values ELSE '');

END

TABLE FILE GGSALES

SUM UNITS

BY ST

BY State

END