I've search the WebFocus forum for something like this, and I've found a couple of posts that seem to do what I want, but I can't quite figure them out. I was hoping somebody could help me with this.

I've got a web form that allows user to enter a comma seperated value list. A possible input would be:

"BKTA-%,XGEM%,DT2-PMX-%,XSWWTW2%"

What I'd like to do is generate a where clause like this:

WHERE FIELD LIKE 'BKTA-%' OR FIELD LIKE 'XGEM%' OR FIELD LIKE 'DT2-PMX-%' OR FIELD LIKE 'XSWWTW2%'

Any idea how I can do that? I would assume I'd need to use GETTOK, but my skills with webfocus are not very good. Any hints would be appreciated.

Chris

GETTOK is correct, try this

  
-SET &incoming = 'BKTA-%,XGEM%,DT2-PMX-%,XSWWTW2%';
-SET &screen =' ';

-SET &CNTR = 0;

-:MYLOOP
-SET &CNTR=&CNTR+1;

-SET &var = GETTOK(&incoming,&incoming.LENGTH,&CNTR,',', 12, 'A12');
-IF &var EQ ' ' GOTO :OUT_LOOP;
-SET &var2 = ' ' | '''' || &var || '''' ;

-SET &screen = IF &CNTR EQ 1 THEN 
- 'WHERE FIELD LIKE ' |  &var2 ELSE
- &screen || ' OR FIELD LIKE ' ||  &var2 ;
-GOTO :MYLOOP
-:OUT_LOOP
-TYPE &screen

quote:

WHERE FIELD LIKE 'BKTA-%' OR FIELD LIKE 'XGEM%' OR FIELD LIKE 'DT2-PMX-%' OR FIELD LIKE 'XSWWTW2%'

Chris, you can shorten this phrase to
WHERE field LIKE 'BKTA-%' OR 'XGEM%' OR 'DT2-PMX-%' OR 'XSWWTW2%'

Chris,
There is a function called STRREP which is used to replace strings. So you could code something like this:
-SET &INSTR='BKTA-%,XGEM%,DT2-PMX-%,XSWWTW2%';
-SET &INSTRLEN=&INSTR.LENGTH;
-SET &OR=''' OR ''';
-SET &WHERE=STRREP(&INSTRLEN, &INSTR, 1, ',', 6, &OR, 200, 'A200');
(I use an output length of 200 for good measure)
Now all the commas have been replaced by ' OR '.
Your WHERE statement will then be:
WHERE FIELD LIKE '&WHERE'
Don't forget the quotes around &WHERE.

Danny

If you use FOCUS datasource, (never used this on other data source), and the LIKE operator, you have to close the final quote in the string up to within the length of the field, after the STRREP add:

-SET &WHERE = '''' || &WHERE || '''';

and use:

WHERE FIELD LIKE &WHERE ;

otherwise the last screening option may fail.

It works okay if you use EQ instead of LIKE.

Alan,
True. I forgot about the trailing blanks.
You could calculate the length of the &WHERE string but your -SET does the trick better.

Alan B,

I tried your suggestion, but I think it kept getting caught in an endless loop.

Danny-SRL,

Thank you sir, for making me look like a fool in front of the entire internet . I was so wrapped up in trying to loop through the structure, that it never occurred to me that all I needed to do was replace the commas with "OR"s.

In fact, I didn't even need to do in WebFocus at all. I treated the string in JavaScript before I sent it to WebFocus for execution.

Thanks for the suggestions! I got the report working now, as I want.

Chris