how to loop a query in webfocus

for example if i am having an ampvariable which has input data separated by commas i want it in the loop in such a way that it has to read the value till it encounters a comma and do the necessary querying and again enter the loop and check for another value and so on.

for example if i am having customerid as ampvariable and i entered the data for ciustomerid as 123,456,789,978,654 then it has to read customerid as 123 and do all the querying and then go back into the loop read customerid as 456 and do all the querying so on till it reads the last number 654.
thanks for ur time and help
maddy

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

Maddy, I did somthing like this. I had an input field that we allowed the user to enter multiple facilities 4 characters in length each up to 15, seperated by a comma. Then we parssed the input field and created a single were statement example:

0123,0401,0650,0444

creates a where statement that looks like this:
IF FACILITY_ID EQ '0123'
OR '0401'
OR '0650'
OR '0444'

Here is the Code


-**********THIS IS THE LOOP TO PARSE FACILITY
-IF &FACILITY_ID.EXIST THEN GOTO FIRSTFAC ELSE GOTO DONEFAC;
-FIRSTFAC
-IF &FACILITY_ID EQ '' THEN GOTO DONEFAC;
-SET &FAC_ID = SUBSTR(&FACILITY_ID.LENGTH,
- &FACILITY_ID,
- 1,4,4,'A4');
IF FACILITY_ID EQ '&FAC_ID'
-IF &FACILITY_ID.LENGTH GT 4 THEN GOTO STARTFAC ELSE GOTO DONEFAC;
-STARTFAC
-SET &LOOPCOUNT = (&FACILITY_ID.LENGTH / 4);
-TYPE &LOOPCOUNT
-REPEAT LOOPFAC FOR &COUNTER FROM 2 TO &LOOPCOUNT
-SET &FAC_ID = SUBSTR(&FACILITY_ID.LENGTH,
- &FACILITY_ID,
- &COUNTER * 5 - 4,&COUNTER * 5 - 1,4,'A4');
OR '&FAC_ID'
-LOOPFAC
-DONEFAC
Hope this Helps

This message has been edited. Last edited by: <Mabel>,

SUBSTR serves the purpose if the token lengths are uniform.

If they vary, you'll can build a solution using either GETTOK() (stopping when it returns an empty value), or a combination of POSIT() to locate the next delimiter and SUBSTR() to isolate the next token and its remainder (stopping when the remainder is blank).

Hi Texas Stingray
thanks for ur help. i tried to use the same logic of your query in mine but its not working .

TABLE FILE SQLOUT
PRINT
CUSTNAME
CUSTID
-IF &CUSTID.EXIST THEN GOTO K1 ELSE GOTO K2;
-K1
-IF &CUSTID EQ '' THEN GOTO K2;
-SET &C_ID= SUBSTR(&CUSTID.LENGTH, '&CUSTID',1 ,4,4, 'A4');
IF CUSTID EQ '&C_ID'
-IF &CUSTID.LENGTH GT 4 THEN GOTO K3 ELSE GOTO K2;
-K3
-SET &LOOPCOUNT = (&CUSTID.LENGTH/4);
-TYPE &LOOPCOUNT
-REPEAT LOOPFAC FOR &COUNTER FROM 2 TO &LOOPCOUNT
-SET &C_ID= SUBSTR(&CUSTID.LENGTH,'&CUSTID',&COUNTER * 5 -4,&COUNTER * 5 - 1,4,'A4');
OR '&C_ID'
-LOOPFAC
-K2
END
When i run this query its asking me to enter values for both cust_id and counter.
and also in the above query is it
IF CUSTID EQ '&C_ID' or WHERE CUSTID EQ '&C_ID' AFTER THE FIRST SET STATEMENT.
THANKS FOR UR HELP
MADDY

Here's a sample of something that was put together at my location. The input &OR_MULT contained multiple organization codes that were either 1, 2 or 3 characters in length. The input was separated by a blank space. Eg. 1 12 123

-*------------------------------------------------------------------------------
-* Pull apart the Organization entries
-SET &TEST_OR1= ' ';
-IF &OR_MULT EQ ' ' GOTO NO_OR;
-SET &OR_MULT = &OR_MULT || '%' ;
-SET &ORCTR = 0;
-SET &OR_LEN= &OR_MULT.LENGTH + 1;
-GET_OR
-SET &ORCTR = &ORCTR + 1;
-SET &OR.&ORCTR=GETTOK(&OR_MULT, &OR_LEN, &ORCTR, ' ', 3, 'A3');

-* ***************************************************************************
-* Added the -IF for the Org EQ ' '. Also commented out the IF test based on
-* (3 * &ORCTR).
-* ***************************************************************************

-IF &OR.&ORCTR NE ' ' THEN GOTO GET_OR;
-SET &ORCTR = &ORCTR + 1;
-SET &OR.&ORCTR=' ';
-SET &ORCNTR=1;

-* ***************************************************************************
-* Added the following to strip the % sign off of the FIRST org id entered
-* to accommodate an org LT 3 characters.
-* Commented out the previously used SET &TEST_OR.ORCNTR.
-* ***************************************************************************

-SET &OR_POS2.&ORCNTR = EDIT(&OR.&ORCNTR,'$9$');
-SET &OR_POS3.&ORCNTR = EDIT(&OR.&ORCNTR,'$$9');
-SET &OR_POS1_2.&ORCNTR = EDIT(&OR.&ORCNTR,'99$');
-SET &OR_POS1.&ORCNTR = EDIT(&OR.&ORCNTR,'9$$');
-SET &TEST_OR.&ORCNTR = IF &OR_POS2.&ORCNTR EQ '%' THEN
- '''' | &OR_POS1.&ORCNTR || '''' ELSE
- IF &OR_POS3.&ORCNTR EQ '%' THEN '''' | &OR_POS1_2.&ORCNTR || '''' ELSE
- '''' | &OR.&ORCNTR || '''';

-BLD_OR_IF

-SET &ORCNTR=&ORCNTR + 1;
-SET &TEST_OR.&ORCNTR=' ';
-IF &OR.&ORCNTR EQ ' ' GOTO DONE_OR_BLD;

-* ***************************************************************************
-* Added the following to strip the % sign off of each subsequent org id entered
-* to accommodate an org LT 3 characters.
-* Commented out the previously used SET &TEST_OR.ORCNTR.
-* ***************************************************************************

-SET &OR_POS2.&ORCNTR = EDIT(&OR.&ORCNTR,'$9$');
-SET &OR_POS3.&ORCNTR = EDIT(&OR.&ORCNTR,'$$9');
-SET &OR_POS1_2.&ORCNTR = EDIT(&OR.&ORCNTR,'99$');
-SET &OR_POS1.&ORCNTR = EDIT(&OR.&ORCNTR,'9$$');
-SET &TEST_OR.&ORCNTR = IF &OR_POS2.&ORCNTR EQ '%' THEN
- ' OR ' | '''' | &OR_POS1.&ORCNTR || '''' ELSE
- IF &OR_POS3.&ORCNTR EQ '%' THEN ' OR ' | '''' | &OR_POS1_2.&ORCNTR || '''' ELSE
- ' OR ' | '''' | &OR.&ORCNTR || '''';
-GOTO BLD_OR_IF

-DONE_OR_BLD

-SET &ORCNTR = &ORCNTR - 1;
-SET &TEST_OR.&ORCNTR = &TEST_OR.&ORCNTR | ';';

-NO_OR

It might look like a mess, but it does work.

Ken

The technique mentioned by Jack should work for you. here's an example

-SET &INFIELD='200,505,267';
-SET &TOKEN=0;
-REPEAT ENDREPEAT 99 TIMES
-SET &TOKEN=&TOKEN+1;
-SET &ID=GETTOK(&INFIELD, &INFIELD.LENGTH, &TOKEN, ',', 3, 'A3')
-IF '&ID.EVAL' EQ ' ' GOTO EXITREPEAT;
-TYPE &ID
-ENDREPEAT
-EXITREPEAT
The length 3 and format 'A3' can be increased to the maximum expected value.

THANKS GUYS FOR UR HELP AND SUGGESTIONS,
IN MY QUERY LOGIC THE USER ENTERS THE INPUT VALUES FOR THE AMP VARIABLE SEPARATED BY COMMAS AND I HAVE TO USE LOOP IN SUCH A WAY THAT I HAVE TO READ EACH INPUT DATA CALCULATE ITS LENGTH AND COMPARE WITH THE DEFAULT LENGTH AND RETURN TRUE IF THE LENGTHS ARE EQUAL AND FALSE IF THE LENGTHS ARE NOT EQUAL.
FOR EXAMPLE
FOR CUSTID THE INPUT DATA IS 1234,5678,9012,123,56789
AND MY DEFAULT DATA LENGTH IS 4 THEN I HAVE TO FIRST TAKE 1234 AND COMPARE IT WITH DEFAULT LENGTH AND THEN RETURN IT AS TRUE AND SIMILARLY READ 123 AND RETURN AS FALSE.
I TRIED ARGLEN AND GETTOK FUNCTIONS IN MY LOOP BUT I COULDNT WORK OUT.
THANKS FOR UR TIME
MADDY

JG, your loop seems to work but when the code exits the loop the value for &ID comes up empty. Any suggestions?

How about writing to a file and then reading each value?

  
-* File maddy01.fex
-* Input string
-SET &STR='123,456,789,978,654';
-* Number of values
-SET &COMMAS=4;
-* Transform a comma into a CRLF pair
-SET &CRLF=HEXBYT(13,'A1') | HEXBYT(10,'A1');
-SET &STRLEN=&STR.LENGTH;
-SET &STRWLEN=&STRLEN+&COMMAS;
-SET &STRW=STRREP(&STRLEN,&STR,1,',',2,&CRLF,&STRWLEN,'A&STRWLEN.EVAL');
-RUN
-* write to a file
FILEDEF LOOPPARMS DISK LOOPPARMS.FTM
-RUN
-WRITE LOOPPARMS &STRW
-RUN
-* Read each value separately
-#LOOP READ LOOPPARMS,&VAL
-IF &IORETURN GOTO #DONE;
-************
-* Enter here the desired code using the value read
-************
-TYPE &VAL
-GOTO #LOOP
-#DONE