Hello all,

i'm looking to take a string that looks like this:

'AAABBBCCCDDD' OR 'EEEFFFGGGHHH' OR 'IIIJJJ***LLL'

Where the 12 characters in the quotes is static (always 12) but the number of segments of 12 characters varies.
I'm trying to build a dynamic where statement that will use the first 3 characters of each segement then the next 3 then the next 3 etc.

So i'm looking to create 4 variables from the above input(*do not add the OR when a single segment is entered):
VARIABLE1 = 'AAA' OR 'EEE' OR 'III';
VARIABLE2 = 'BBB' OR 'FFF' OR 'JJJ';
VARIABLE3 = 'CCC' OR 'GGG' OR '***';
VARIABLE4 = 'DDD' OR 'HHH' OR 'LLL';

Use the created variables in a where statement:
WHERE FIELD1 EQ &VARIABLE1;
WHERE FIELD2 EQ &VARIABLE2;
WHERE FIELD3 EQ &VARIABLE3;
WHERE FIELD4 EQ &VARIABLE4;


Thanks ahead!!

This message has been edited. Last edited by: Joe Beydoun,

You can use GETTOK to tokenize your string based on ' OR '.

By looping through each token, you can then get rid of the enclosing quotes (TRIM can help you there) and then use EDIT to get the first 3 characters.

Just use each resulting variable in a WHILE statement on each iteraction of the loop .. as FIELD1, FIELD2, etc. is also a "variable" field name just use a different set of &variable to make that up (as you're already looping it will be very simple to use something like:

-SET &I = 0;

TABLE FILE CAR
PRINT ....
BY ....

-REPEAT :DOSTUFF ...
-SET &I = &I + 1;
-SET &TOKEN.&I = EDIT/TRIM/GETTOK(....)
...
WHILE FIELD&I EQ &TOKEN.&I ...
...
-:DOSTUFF
ON TABLE ...
END

There is great documentation about each of those functions both with the product and at this forum so you'll likely find useful ideas.

Oh, I did not pay attention to your "groups of 3" in the initial post.

That means you'll need a slightly more elaborated version of the logic implementing a "group" variable that would increase for every 3 tokens. Your WHERE statement won't use each token directly but perhaps a "token group" variable ... sounds horrible to describe but the code should be relatively simple.

This will pull apart the string.

-SET &CNTR = 0 ;

-SET &STRING = '''AAABBBCCCDDD'' OR ''EEEFFFGGGHHH'' OR ''IIIJJJ***LLL''' ;

-SET &NO_QUOTE = STRIP(&STRING.LENGTH,&STRING,'''','A&STRING.LENGTH') ;

-SET &NO_OR    = STRREP (&NO_QUOTE.LENGTH,&NO_QUOTE,4,' OR ',1,',',&NO_QUOTE.LENGTH,'A&NO_QUOTE.LENGTH') ;

-TYPE &STRING
-TYPE &NO_QUOTE
-TYPE &NO_OR

-REPEAT GET_TOK FOR &CNTR FROM 1 TO 100 ;

-SET &ITEM = GETTOK(&NO_OR,&NO_OR.LENGTH,&CNTR,',',12,'A12') ;

-SET &CNTR = IF &ITEM EQ ' ' THEN 101 ELSE &CNTR ;
-IF &CNTR EQ 101 THEN GOTO GET_TOK ;

-SET &ITEM_1_.&CNTR = EDIT(&ITEM,'999$$$$$$$$$') ;
-SET &ITEM_2_.&CNTR = EDIT(&ITEM,'$$$999$$$$$$') ;
-SET &ITEM_3_.&CNTR = EDIT(&ITEM,'$$$$$$999$$$') ;
-SET &ITEM_4_.&CNTR = EDIT(&ITEM,'$$$$$$$$$999') ;

-GET_TOK

-? &ITEM

Thanks Waz!

This may have solved another issue for me.
As for this one, one more step of creating the Where statements using those variables.

WHERE FIELD1 EQ '&ITEM_1_1' OR '&ITEM_1_2' OR '&ITEM_1_3';
WHERE FIELD2 EQ '&ITEM_2_1' OR '&ITEM_2_2' OR '&ITEM_2_3';
WHERE FIELD3 EQ '&ITEM_3_1' OR '&ITEM_3_2' OR '&ITEM_3_3';
WHERE FIELD4 EQ '&ITEM_4_1' OR '&ITEM_4_2' OR '&ITEM_4_3';

quote:

one more step of creating the Where statements using those variables

The WHERE Clauses will be the easy bit.

Here's another version of the example Waz started. This creates the where statements based on the input string.

-*
-SET &STRING = '''AAA***CCC***'' OR ''EEEFFF******'' OR ''IIIJJJ******''' ;
-*
-SET &NO_QUOTE = STRIP(&STRING.LENGTH,&STRING,'''','A&STRING.LENGTH') ;
-SET &NO_OR = STRREP (&NO_QUOTE.LENGTH,&NO_QUOTE,4,' OR ',1,',',&NO_QUOTE.LENGTH,'A&NO_QUOTE.LENGTH') ;
-*
-TYPE ----------------------------------------------------------------------------
-TYPE STRING ------- &STRING
-TYPE NO_QUOTE ----- &NO_QUOTE
-TYPE NO_OR -------- &NO_OR
-TYPE ----------------------------------------------------------------------------
-*
-SET &CNTR = 0;
-SET &P1_CNT = 0;
-SET &P1_TXT = 'ALL';
-SET &P2_CNT = 0;
-SET &P2_TXT = 'ALL';
-SET &P3_CNT = 0;
-SET &P3_TXT = 'ALL';
-SET &P4_CNT = 0;
-SET &P4_TXT = 'ALL';
-*
-TYPE ---------- LOOP BEGIN ----------
-:LOOP_BEG
-*
-SET &CNTR = &CNTR + 1;
-SET &ITEM = GETTOK(&NO_OR,&NO_OR.LENGTH,&CNTR,',',12,'A12');
-*
-IF &ITEM EQ ' ' THEN GOTO :LOOP_END;
-*
-SET &P1_VAL = EDIT(&ITEM,'999$$$$$$$$$');
-SET &P2_VAL = EDIT(&ITEM,'$$$999$$$$$$');
-SET &P3_VAL = EDIT(&ITEM,'$$$$$$999$$$');
-SET &P4_VAL = EDIT(&ITEM,'$$$$$$$$$999');
-*
-TYPE CNTR -------- &CNTR
-TYPE ITEM -------- &ITEM
-TYPE P1_VAL ------ &P1_VAL
-TYPE P2_VAL ------ &P2_VAL
-TYPE P3_VAL ------ &P3_VAL
-TYPE P4_VAL ------ &P4_VAL
-*
-SET &P1_CNT = IF &P1_VAL EQ '***' THEN &P1_CNT ELSE &P1_CNT + 1;
-SET &P1_TXT = IF &P1_VAL EQ '***' THEN &P1_TXT ELSE IF &P1_CNT EQ 1 THEN '''' | &P1_VAL | '''' ELSE &P1_TXT | ',' | '''' | &P1_VAL | '''';
-*
-SET &P2_CNT = IF &P2_VAL EQ '***' THEN &P2_CNT ELSE &P2_CNT + 1;
-SET &P2_TXT = IF &P2_VAL EQ '***' THEN &P2_TXT ELSE IF &P2_CNT EQ 1 THEN '''' | &P2_VAL | '''' ELSE &P2_TXT | ',' | '''' | &P2_VAL | '''';
-*
-SET &P3_CNT = IF &P3_VAL EQ '***' THEN &P3_CNT ELSE &P3_CNT + 1;
-SET &P3_TXT = IF &P3_VAL EQ '***' THEN &P3_TXT ELSE IF &P3_CNT EQ 1 THEN '''' | &P3_VAL | '''' ELSE &P3_TXT | ',' | '''' | &P3_VAL | '''';
-*
-SET &P4_CNT = IF &P4_VAL EQ '***' THEN &P4_CNT ELSE &P4_CNT + 1;
-SET &P4_TXT = IF &P4_VAL EQ '***' THEN &P4_TXT ELSE IF &P4_CNT EQ 1 THEN '''' | &P4_VAL | '''' ELSE &P4_TXT | ',' | '''' | &P4_VAL | '''';
-*
-GOTO :LOOP_BEG
-:LOOP_END
-TYPE ---------- LOOP END ------------
-*
-SET &P1_WHE = IF &P1_CNT EQ 0 THEN '' ELSE IF &P1_CNT EQ 1 THEN 'WHERE FIELD1 EQ ' | &P1_TXT ELSE 'WHERE FIELD1 IN (' | &P1_TXT | ')';
-SET &P2_WHE = IF &P2_CNT EQ 0 THEN '' ELSE IF &P2_CNT EQ 1 THEN 'WHERE FIELD2 EQ ' | &P2_TXT ELSE 'WHERE FIELD2 IN (' | &P2_TXT | ')';
-SET &P3_WHE = IF &P3_CNT EQ 0 THEN '' ELSE IF &P3_CNT EQ 1 THEN 'WHERE FIELD3 EQ ' | &P3_TXT ELSE 'WHERE FIELD3 IN (' | &P3_TXT | ')';
-SET &P4_WHE = IF &P4_CNT EQ 0 THEN '' ELSE IF &P4_CNT EQ 1 THEN 'WHERE FIELD4 EQ ' | &P4_TXT ELSE 'WHERE FIELD4 IN (' | &P4_TXT | ')';
-*
-TYPE ----------------------------------------------------------------------------
-TYPE P1_CNT ------ &P1_CNT
-TYPE P1_TXT ------ &P1_TXT
-TYPE P2_CNT ------ &P2_CNT
-TYPE P2_TXT ------ &P2_TXT
-TYPE P3_CNT ------ &P3_CNT
-TYPE P3_TXT ------ &P3_TXT
-TYPE P4_CNT ------ &P4_CNT
-TYPE P4_TXT ------ &P4_TXT
-TYPE ------------------------ GENERATED WHERE STATEMENTS ------------------------
-TYPE P1_WHE ------ &P1_WHE
-TYPE P2_WHE ------ &P2_WHE
-TYPE P3_WHE ------ &P3_WHE
-TYPE P4_WHE ------ &P4_WHE
-TYPE ----------------------------------------------------------------------------

Maybe this will help!
~Jim

Thanks All, I was able to accomplish what i needed!