Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Ricardo Lara

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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' ...

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...