Hi,

I'm looking for the most efficient way to implement a dynamic where-clause , this method should also be able use CONTAINS (meaning i should be able to search on a partial string)

Any Ideas???

Thanks

Peter

Peter
Here's a simple example using the MOVIES file:

MAINTAIN FILE MOVIES
COMPUTE VAL/A0 = 'ROOF';
REPOSITION MOVIECODE
FOR ALL NEXT MOVIECODE INTO STK
WHERE TITLE CONTAINS VAL;
COMPUTE I/I2=1;
REPEAT STK.FOCCOUNT
TYPE stk(i).TITLE;
COMPUTE I=I+1;
ENDREPEAT
END

Yields:
FIDDLER ON THE ROOF
CAT ON A HOT TIN ROOF

Here, I am hardcoding the value for VAL, but you can place the field on a form and a user can enter or select. You can also pass it in.

Notice that the format of VAL is A0. This is an unlimited alpha field. It is as big as the data. This way you don't have to worry about trailing blanks.

Mark

Hi Mark,

thats surely the way to go , but i have more than one input field, but i dont seem to be able to use more than one where clause .

Peter

This is an example of where i wanna go ...

stack clear stksloc
compute val/A0=ShwLocation.S_ClientName.text;
compute val2/A0='IT';
reposition id
for all next id into stksloc
where (client_name contains val or client_name contains val2);
compute i=1;
repeat stksloc.foccount
compute i=i+1;
endrepeat


Is there any way to make the OR mechanism work???

Peter.

And an even bigger problem seems to be that the client_name in the DB is sometimes lower or uppercase
so that this routine doesnt catch them both

where (client_name contains val or client_name contains val2);

I have imported the MNTUWS module which embeds the upcase routine

but dont seem to be able to do :

where (upcase(client_name) contains val or client_name contains val2);

what am i missing here????

It just isnt my day

Peter

There are two ways to accomplish this. You will have to decide which one is better at your site. We will use UPCASE and LOWER to change the case of input values. We will have to test for both cases. If you need MIXED case, you can use LCWORD to get that as well.

The first technique loads ALL the records into a stack, and then uses STACK copy to get the ones you want.

MAINTAIN FILE MOVIES
MODULE IMPORT(MNTUWS)

COMPUTE VAL1/A0 = 'Roof';
COMPUTE VAL2/A0 = 'How To';
Compute Val1U/a0 = Upcase(VAL1);
Compute Val1L/10 = Lower(VAL1);
Compute Val2U/a0 = Upcase(VAL2);
Compute Val2L/10 = Lower(VAL2);

Infer Moviecode into MOVSTK
For all next moviecode into ALLSTK

STACK COPY FROM ALLSTK INTO MOVSTK
WHERE (TITLE CONTAINS VAL1U)
OR (TITLE CONTAINS VAL1L)
OR (TITLE CONTAINS VAL2U)
OR (TITLE CONTAINS VAL2L)
END

The second techinque uses a different NEXT statement for each condition. It loads less records that the first technique, but hits the database 4 times:

MAINTAIN FILE MOVIES
MODULE IMPORT(MNTUWS)

COMPUTE VAL1/A0 = 'Roof';
COMPUTE VAL2/A0 = 'How To';
Compute Val1U/a0 = Upcase(VAL1);
Compute Val1L/10 = Lower(VAL1);
Compute Val2U/a0 = Upcase(VAL2);
Compute Val2L/10 = Lower(VAL2);

Reposition Moviecode
For all next moviecode into MOVSTK(Movstk.foccount+1)
WHERE (TITLE CONTAINS VAL1U)
Reposition Moviecode
For all next moviecode into MOVSTK(Movstk.foccount+1)
WHERE (TITLE CONTAINS VAL1L)
Reposition Moviecode
For all next moviecode into MOVSTK(Movstk.foccount+1)
WHERE (TITLE CONTAINS VAL2U)
Reposition Moviecode
For all next moviecode into MOVSTK(Movstk.foccount+1)
WHERE (TITLE CONTAINS VAL2L)

END

I hope this helps - Mark

Hi Mark,

Once again you saved my day

this is really stuff i can work with.

I also just found a technique in which they make a webfocus report with all the fields you need

Call it with a EXEC function and pass the input fields along with it and put the output in stack

Is this a wise methode ? any drawbacks???

Thanks

Peter

There is no draw back to using EXEC. I am sorry that I didn't mention it. You can do that 1 of 2 ways as well. You can JUST pass the 4 parameters that you want or you can build the 4 WHERE clauses and send them.

Using TABLE to do retrieval is VERY efficient. If you run into any problems, just let me know.

Mark

I have been working with it , this weekend
and it seems to work fine
only drawback with all methods seems to be that with very large queries that the screen turns white (flashes)

any way to get around this?


Peter

Not really. The form will refresh when the query is done. Displaying fewer records / objects on the form will cause it to refresh faster.

Mark

Just checking


thanks

Peter