Hello,
I am creating a hold file like this:
ON TABLE HOLD AS BABEMRN_MEAS FORMAT ALPHA

then doing a read statement like this:
-READ BABEMRN_MEAS, &BABY_MEAS

Right now there is only one value in the &BABY_MEAS variable, because there is only one value in the BABEMRN_MEAS table. What if there were two values in the BABEMRN_MEAS table and I needed to capture them both?

You would read the Hold file in a loop and put the variable into an indexed Amper variable. e.g.
-SET &N=1 ;
-REPEAT FRED 2 TIMES
-READ HOLD &VAL.&N
-SET &N=&N+1;
-FRED

Here's an example using the CAR file, reading the hold file and setting variables

TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS HOLD_CAR FORMAT ALPHA
END
-RUN
-SET &V_COUNTRY = ' ';
-SET &V_SALES = 0;
-TYPE START LOOP
-TYPE -------------------------------------------
-REPEAT :LOOP &LINES TIMES
-READ HOLD_CAR &V_COUNTRY.A10. &V_SALES.I6.
-TYPE V_COUNTRY = &V_COUNTRY --- V_SALES = &V_SALES
-:LOOP
-TYPE -------------------------------------------
-TYPE END LOOP

Jim

This is excellent code and it is moving me in the right direction, but I don't think I made my objective clear. Later on in my code I have to put these variables into the header of my PDF file. So, one day, I could have two variables, the next day, three... up to five. So, I kind of need some way to say "if this variable exists then put it in the header..."

How about something like this to put your values into a focexec. In this example I use the CAR file, but you get the idea:

FILEDEF HEADTXT DISK headtxt.fex
SET HOLDLIST=PRINTONLY
DEFINE FILE CAR
HEADLINE/A80 = '"' | COUNTRY | '"';
END
TABLE FILE CAR
PRINT
HEADLINE
ON TABLE SAVE AS HEADTXT
END
-RUN

and then later on -INCLUDE the HEADTXT fex into your report header.

Hey,
This is great information. I just want to know if there is another format I can save my hold file as, instead of alpha, so that I don't have to know the exact number of spaces to read for each field?

how about comma delimited?

but the suggestion you have got from JBMUIR is gives you maybe just what you want.

Mark,

You can use the alternate form of the -READ statement where you do not have to identify the length -

TABLE FILE CAR
BY MODEL
ON TABLE SAVE AS MODELS
END
-RUN
-SET &ModelCnt = &LINES;
-SET &N = 1;
-REPEAT :Loop &LINES TIMES;
-READ MODELS, &Model.&N
-SET &N = &N + 1;
-:Loop
-SET &ModelCnt = &N - 1;
-? &Model

The important piece is the comma after the save or hold file name -
-READ MODELS,
and &ModelCnt will give you the number of amper vars (there are two places to achieve this shown).

T

This message has been edited. Last edited by: Tony A,

Apologies, you wanted to display all of the variables in a heading -

TABLE FILE CAR
BY COUNTRY
ON TABLE SAVE AS COUNTRYS
END
-RUN
-SET &N = 1;
-REPEAT :Loop1 &LINES TIMES;
-READ COUNTRYS, &Country.&N
-SET &N = &N + 1;
-:Loop1
-SET &CountryCnt = &N - 1;
-? &Country
TABLE FILE GGSALES
BY REGION NOPRINT
IF RECORDLIMIT EQ 1
HEADING
"
-SET &N = 1;
-REPEAT :Loop2 &CountryCnt TIMES;
&Country.&N <0X
-SET &N = &N + 1;
-:Loop2
"
END

Will do this for you using my suggestion above, or

FILEDEF HEADTXT DISK headtxt.fex
SET HOLDLIST=PRINTONLY
DEFINE FILE CAR
HEADLINE/A80 = '"' | COUNTRY | '"';
END
TABLE FILE CAR
BY HEADLINE
ON TABLE SAVE AS HEADTXT
END
-RUN
TABLE FILE GGSALES
BY REGION NOPRINT
IF RECORDLIMIT EQ 1
HEADING
-INCLUDE HEADTXT
END

Using JbMuirs.

T

Edited as I forgot the line continuation syntax for the first sample

This message has been edited. Last edited by: Tony A,

After my edit of the last post, I got to thinking that you could achieve the same, one line header, using JBs suggestion -

FILEDEF HEADTXT DISK headtxt.fex
SET HOLDLIST=PRINTONLY
DEFINE FILE CAR
HEADLINE/A80 = COUNTRY || ', <0X';
END
TABLE FILE CAR
BY HEADLINE
ON TABLE SAVE AS HEADTXT
END
-RUN
TABLE FILE GGSALES
BY REGION NOPRINT
IF RECORDLIMIT EQ 1
HEADING
"
-INCLUDE HEADTXT
"
END

Note that you will receive a FOC227 - "The Focexec Procedure cannot be found" error on running this. The reason is that the fex doesn't exist at the time the dialogue manager (any line begining with a "-") is processed, which is before the TABLE request that produces it. Therefore, in this instance, it is not a problem, per se.

T

This is awesome, but still something bugs me about the SET HOLDLIST=PRINTONLY. I've never used that before, so I looked it up, but I cannot tell from the reference manual why it makes a difference in this situation. I tried running Tony's last program without the HOLDLIST parameter and of course the output was the hold file table specs. I'm sure that there is some kind of deal between the HOLDLIST and the -RUN statement that makes the difference. Can anybody explain it to me though? Thanks!

Mark,

You don't actually require the HOLDLIST setting in this situation, I only included it as it was there in JBs sample and I was too lazy to remove it

HOLDLIST can be useful when you have something like

TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY NOPRINT
BY CAR
BY MODEL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS MYSAVE
END

In this instance the hold file would have columns for Car, Model, Retail_Cost and Dealer_Cost only.

T

OK, got another question. What if I have two variables in my hold file and I need not only each row to be a different amper variable, but also each column. My code that I attempted to do this with was like this:

TABLE FILE BABEMRN
PRINT MOMMRN BABYMRN
ON TABLE SAVE AS MYBABY
END
-RUN
-SET &NUMRECS = &LINES;
-SET &N = 1;
-REPEAT :LOOP &LINES TIMES;
-READ MYBABY, &MOMMRN.&N, &BABYMRN.&N
-SET &N = &N + 1;
-:LOOP
-SET &NUMRECS = &N - 1;
-? &MOMMRN
-? &BABYMRN
-EXIT

And what it gave me was this:

ALPHANUMERIC RECORD NAMED MYBABY
0 FIELDNAME ALIAS FORMAT LENGTH
MOMMRN E01 A18V 24
BABYMRN E04 A18V 24
TOTAL 48
CURRENTLY DEFINED & VARIABLES STARTING WITH 'MOMMRN':
&MOMMRN1 = 00000AAAAAAA .
&MOMMRN2 = 00000BBBBBBB .
&MOMMRN3 = 00000CCCCCCC .
&MOMMRN4 = 00000DDDDDDD .
&MOMMRN5 = 00000EEEEEEE .
&MOMMRN6 = 00000AAAAAAA .
&MOMMRN7 = 00000BBBBBBB .
&MOMMRN8 = 00000CCCCCCC .
&MOMMRN9 = 00000DDDDDDD .
CURRENTLY DEFINED & VARIABLES STARTING WITH 'BABYMRN':
&BABYMRN1 = 00000AAAAAAA .
&BABYMRN2 = 00000BBBBBBB 00000XXXXXXX
&BABYMRN3 = 00000CCCCCCC .
&BABYMRN4 = 00000DDDDDDD .
&BABYMRN6 = 00000AAAAAAA .
&BABYMRN7 = 00000BBBBBBB 00000XXXXXXX
&BABYMRN8 = 00000CCCCCCC .
&BABYMRN9 = 00000DDDDDDD .

If you want to -READ more than one variable per row, you either have to specify the size of the variable (&VAR1.a10.) or read one variable (as per Tony's example) and then separate that one into two via a substring function working with a delimiter between the two pieces.

Or you could force the variable lengths so that you know what length to read in -

TABLE FILE BABEMRN
PRINT MOMMRN/A18
      BABYMRN/A18
ON TABLE SAVE AS MYBABY
END
-RUN
...
-READ MYBABY &MOMMRN.&N.A18. &BABYMRN.&N.A18.
...

T

Another example:

TABLE FILE CAR
PRINT
CAR
COMPUTE DELIMITER/A1 = '~';
MODEL
ON TABLE SAVE AS SAVE1
END
-RUN

-REPEAT END_REP1 FOR &COUNTER FROM 1 TO &LINES

-READ SAVE1, &SAVE1DATA.&COUNTER

-SET &SAVE1A.&COUNTER = 
-  GETTOK(&SAVE1DATA.&COUNTER, &SAVE1DATA.&COUNTER.LENGTH, 1, '~', &SAVE1DATA.&COUNTER.LENGTH, 'A.&SAVE1DATA.&COUNTER.LENGTH');
-SET &SAVE1B.&COUNTER = 
-  GETTOK(&SAVE1DATA.&COUNTER, &SAVE1DATA.&COUNTER.LENGTH, 2, '~', &SAVE1DATA.&COUNTER.LENGTH, 'A.&SAVE1DATA.&COUNTER.LENGTH');
-END_REP1

-? &SAVE1DATA
-? &SAVE1A
-? &SAVE1B

So, would you do that same kind of thing with a comma delimited file? The GETTOK function?

Perhaps. If you have a Master for it that describes each field in the comma delimited file, it might be easier to use it to separate the values.

Mark, are there some reasons why you don't want to use the lengths of the columns? There are a lot of good suggestions in here. Here's another method that will work as well:

TABLE FILE CAR
PRINT COUNTRY
ON TABLE SAVE AS CNTRY
END
-RUN
-SET &TOTLINES = &LINES ;

-SET &COUNTRY = ' ';
-SET &CNTR = 0 ;
-REPEAT :LOOP WHILE &CNTR LT &TOTLINES ;
-SET &CNTR = &CNTR + 1 ;
-READ CNTRY &COUNTRY.&CNTR.A10.
-TYPE COUNTRY &COUNTRY.&CNTR
-:LOOP

-SET &CNTR = 0 ;

-SET &HEADTEXT = ' ';
-REPEAT :LOOP2 WHILE &CNTR LT &TOTLINES ;
-SET &CNTR = &CNTR + 1 ;
-SET &HEADTEXT = &HEADTEXT || ' ' | &COUNTRY.&CNTR ;
-:LOOP2

TABLE FILE CAR
HEADING
"ALL COUNTRIES : &HEADTEXT "
PRINT *
END

All countries are displayed on the same line in this example as well.

OK, I think I am almost there, but I've got one last issue. Right now, I've got an amper variable for each BABYMRN... &babymrn1, &babymrn2. Now, I need those to be seperate on the corresponding headers for each page of my report. So, what I have done is say on the footer of every page saying:

FOOTING BOTTOM
"BABYMRN: &BABYMRN&TABPAGENO"

Thinking that for the first page, the &BABYMRN1 variable will be in the footer of the first page, &BABYMRN2 will be in the footer of the second page, etc....,

Is this possible? Is there an easier way? Thank you!!!!!

Aren't your babies a column in the table you're reading? That's what should be in the Footing. What you're doing will not work.

Mark, if you want to have a value on each individual page, then why are you even bothering with the & variables at all? If you have the column in a sort column and then issue

ON SORTCOLUMN PAGE-BREAK

you'll get the data on separate pages.

Using the car file

TABLE FILE CAR
HEADING
"COUNTRY :
SUM RETAIL_COST
BY COUNTRY PAGE-BREAK
END

I'm really confused as to why you'd bother to go through all this other processing if this is the end state you're working towards.

Ken

Prod - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE
Dev - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE
Databases: Oracle 10g, SQL Server 2000, DB2.

The heading is not coming out properly because of the left caret. It should be

HEADING
"COUNTRY LABEL : (lc)COUNTRY"

WHERE (lc) is the left caret

That's the thing - the table file that I'm printing is different, but needs to have the Baby MRN on each corresponding page. I originally wanted to do a join back to the original data based on three different join fields, but it would be complicated processing with several hold files in order to get the output just right. That's why I thought that amper variables would be easier.

Let's figure why it would be complicated with JOINs and several HOLD files.

I guess I really mean inefficient, perhaps? I would join back to the original file and then would have to use some kind of recursive join to spread that BABYMRN value over all corresponding records. So after the join, my table would be:
MOM,BABYMRN,MEASURE
A,,X
A,123,Y
A,,Z
A,,XX
Because I would be joining back to the original data based on MOM and MEASURE.

But I need to spread that BABYMRN out so:
MOM,BABYMRN,MEASURE
A,123,X
A,123,Y
A,123,Z
A,123,XX

Yikes, I'm floating awayyyyyy.....