November 03, 2005, 06:00 AM
PeteMAINTAIN: Dynamic Where clause
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
November 03, 2005, 11:27 AM
Maintain WizardPeter
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
November 04, 2005, 05:25 AM
PeteHi 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
November 04, 2005, 06:47 AM
PeteThis 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.
November 04, 2005, 08:27 AM
PeteAnd 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
November 04, 2005, 11:47 AM
Maintain WizardThere 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
November 04, 2005, 12:35 PM
PeteHi 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
November 07, 2005, 12:37 PM
Maintain WizardThere 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
November 07, 2005, 01:01 PM
PeteI 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
November 08, 2005, 11:47 AM
Maintain WizardNot really. The form will refresh when the query is done. Displaying fewer records / objects on the form will cause it to refresh faster.
Mark
November 08, 2005, 11:51 AM
PeteJust checking
thanks
Peter