Hi Susannah, I have read through the many discussions regarding looping and this one in particular caught my eye:

-SET &KOUNTER = 0 ;
-REPEAT end.loop 3 TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
-SET &CUR=DECODE &KOUNTER( 1 'EU' 2 'GBP' 3 'USD');
... insert the body of your fex here, using the parameter &CUR however you need to use it.
-RUN
-end.loop
-eoj.thisfex

You sent it awhile back and I was wondering how a looping would work in cases where I wanted to append or concatenate a list.

For example I have a list of 5 ssns in 5 different rows in a hold file. How can I loop through that list and append each one to the end of the other with a comma delimiter and store it in one variable name?

Is this possible?

I have tried the following among many other ways and this is giving me an error... along with the other trials:

-SET &CNTR = 0 ;
-*-DEFAULT &PUNCT = ','
-DEFAULT &NAT =
-REPEAT DEF1 &LINES TIMES
-READ SSNFEED &THISFIELD.A11
&THISFIELD
-DEF1

It seems to be reading the hold file correctly but I don't think they are appending to each other.

This is my hold file called SSNFEED
SSN
111111111
222222222
333333333
444444444
555555555

When I loop I want to set the parameter & NAT = the list of these:

-SET &NAT = (111111111,222222222,333333333,444444444,555555555);

Is this possible?

Thank you in advance!

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

sure
start with
-SET &PUNCT = ''; Nope..THIS wont work..can't have a true empty...
-SET &NAT = '(' ;
then enter your loop
and after each -READ of your hold file
-SET &NAT = &NAT | &PUNCT | &THISFIELD ; nope (see below)
and now after your first read,
-SET &PUNCT = ',';

but dont loop by &LINES
thats a system var
hold your &LINES in your own variable
-SET &HOWMANY = &LINES ;
and then REPEAT DEF1 &HOWMANY TIMES
(that may have been why you were getting an error)
and when you finish,
-SET &NAT = &NAT | ')' ;
to close it off, if i read what you want to do correctly
-*-------------------------------------- better
-SET &NAT = '(';
-SET &KOUNTER = 0 ;
-REPEAT end.loop &HOWMANY TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
... do your -READ
-SET &NAT = &NAT | &THISFIELD ;
-IF &KOUNTER EQ &HOWMANY GOTO end.loop ;
-SET &NAT = &NAT | ',';
-end.loop
-SET &NAT = &NAT | ')';

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

That worked perfectly except is there a limit that Web Focus has? I am getting an HTTP 500 server error occurred and I'm pretty sure it is the amount of ssn's I am trying to concatenate.

I tried it where I limited the number to a small one and it worked perfectly... Do you know what the number limit it would take? I assume it would be the amount of max characters a variable could hold. For this particular file I have 924 ssns. So I am thinking that I would instead use the loop to feed one ssn in the query and then hold the info and then feed the next ssn in the query and append that info to the same hold file as the first one. Does that make sense?

This is what I have now so that I can feed it in one at a time:

-SET &HOWMANY = &LINES ;

-SET &KOUNTER = 0 ;
-REPEAT end.loop &HOWMANY TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
-READ SSNHOBSFEED &THISFIELD.A13
-SET &NAT = &THISFIELD ;
-IF &KOUNTER EQ &HOWMANY GOTO end.loop ;

-INCLUDE LIT_SETENV
SQL SQLORA
SELECT B.ADDRESS_LINE1, B.ADDRESS_LINE2, B.ADDRESS_LINE3, B.TOWN_OR_CITY, B.POSTAL_CODE, B.COUNTRY, B.REGION_2
FROM ADDRESSES B , ALL_PEOPLE_F C
WHERE B.PERSON_ID = C.PERSON_ID
AND C.NATIONAL_IDENTIFIER IN &NAT
AND B.PRIMARY_FLAG = 'Y';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOBSADDR
END

-end.loop
-EXIT

That works to feed each ssn in one by one, but when I try to append that info I get either the ssn being used over or it doesn't append. I have tried:

-IF &KOUNTER EQ 1 THEN GOTO FIRST
-ELSE GOTO NEXT;

-FIRST
FILEDEF HOLDKEEP DISK HOLDKEEP.FTM
-RUN
-GOTO HOBSADDR;

-NEXT
FILEDEF HOLDKEEP DISK HOLDKEEP.FTM (append
-RUN
-HOBSADDR
TABLE FILE HOBSADDR
PRINT *
ON TABLE HOLD AS HOLDKEEP FORMAT ALPHA
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END
-IF &KOUNTER EQ &HOWMANY GOTO end.loop ;
-end.loop

For appending after that but that isn't working. Any ideas anyone?

Thank you so much for all your help Susanne and everyone.

Thank you for your help, it was very useful!

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

slfmr,

Might I ask what you intend to do with this string? There may be a better way of doing what you are attempting depending upon your answer.

For instance, if you are going to use it in an SQL IN statement e.g. WHERE column IN &variable, then you may be better off using a subselect e.g. WHERE column IN (SELECT column FROM table WHERE etc...).

T

Well since I remembered that there was a limit on how many IN parameters you can have, I have decided to go against looping to create the IN statement. Instead what I am trying to do now is loop so that it feeds the query 1 ssn at a time, stores that information in a hold file and then goes to the next ssn, does the same thing, but appends it to the same hold file as the first one.

The problem I am having now is the appending. I am able to loop the ssns individually in the query and put that info on a hold file, but it is only saving and displaying the last ssn's data that was retrieved.

I hope this makes sense.

So this works for the looping, but I'm not sure what I can add to save the data and then append the new data to the same hold file. Is that possible? I'm sure it is, but I could be wrong.

-SET &HOWMANY = &LINES + 1 ;

-SET &KOUNTER = 0 ;
-REPEAT end.loop &HOWMANY TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
-READ SSNHOBSFEED &THISFIELD.A13
-SET &NAT = &THISFIELD ;
-IF &KOUNTER EQ &HOWMANY GOTO end.loop ;

-INCLUDE LIT_SETENV
SQL SQLORA
SELECT B.ADDRESS_LINE1, B.ADDRESS_LINE2, B.ADDRESS_LINE3, B.TOWN_OR_CITY, B.POSTAL_CODE, B.COUNTRY, B.REGION_2
FROM PER_ADDRESSES@BEFG B , PER_ALL_PEOPLE_F@BEFG C
WHERE B.PERSON_ID = C.PERSON_ID
AND C.NATIONAL_IDENTIFIER IN &NAT
AND B.PRIMARY_FLAG = 'Y'
AND SYSDATE BETWEEN B.DATE_FROM AND NVL(B.DATE_TO, SYSDATE)
AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOBSADDR
END
-end.loop
-EXIT

Thank you so much for your response!

appending a bunch of new records to a hold file is a piece o' cake. use the MORE command
table file car
print *
if country ne 'england'
on table hold as europe
end
table file car
print *
if country eq 'england'
on table hold as england
end
table file europe
print *
on table hold as bigfile
more
file england
end
Was that your question???

Yes, that is what I was asking. I had been trying to use the FILEDEF to append because I didn't know the names of what my holdfiles would be and with the MORE, it looked like that was what I needed.

Instead I ended up saving each query output on a Hold file called HOLD||&HOWMANY... so for 3 ssns it would be hold1, hold2, and hold3.

Then I added this after:

table file HOLD1
print *
on table hold as holdall

created a loop here so that it would append
MORE
FILE &NAME

-*where the &NAME is hold2, or hold3 depending on the count at the time.. so it created the MORE statements on the fly.

All in all with all your help I found a solution and I can't thank you enough!

well, now there is a FILEDEF APPEND method, too, since you sorta mentioned it.
FILEDEF MYFILE DISK MYFILE.FTM
table file somefile
print SSN
on table hold as myfile
end
FILEDEF MYFILE DISK MYFILE.FTM (APPEND
table file someotherfile
print SSN
on table hold as myfile
end
..try this out..the first time thru as i recall you have to just filedef your output area then write to it, then re-issue the filedef with the append attribute and then keep writing to it.
Do a knowledgebase search on FILEDEF (APPEND and see what you come up with.[i'm doing this from memory, which is fuzzy]
You might find it works better for your application that the MORE thing.

ps: you know about
-READ filename NOCLOSE &THISFIELD.A8
that noclose command keeps the -read in your loop from reading the same opening record a zillion times;

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

Great thanks, I will try this out also. It's something i have used before when I was first learning.

That NOCLOSE will help as well for future purposes.

Again, thank you so much!