[ASK] Dynamic Where Clause

G

gate_hongs46

New Member
Greetings.

im trying to make a dynamic where for searching data.
Can i code like this:

def var fieldv as char.

fieldv = browse-name:CURRENT-COLUMN:name.

{find-data.i
&TABLE = "customer"
&FIELD = fieldv
&SEARCH = f-search:SCREEN-VALUE}

find-data.i:
FIND FIRST {&TABLE} WHERE {&FIELD} BEGINS {&SEARCH} NO-LOCK NO-ERROR.
if avail {&TABLE} then reposition browse-name to recid recid({&TABLE}) no-error.

it seems to be not working.
anything wrong with my coding.
need help. thx.
 
What Progress Version you're on?

Your code is all but dynamic as it uses include file technique which is resovled at compile time, _NOT_ at runtime.

HTH, RealHeavyDude.
 
You should look at dynamic programming, somthing like this:

<code-snipet>
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.
ASSIGN cTableName = "Customer"
cFieldName = browse-name:CURRENT-COLUMN:NAME.
CREATE BUFFER hBuffer FOR TABLE cTableName.
ASSIGN lOkay = hBuffer:FIND-FIRST ( "WHERE " + cFieldName + " BEGINS " + f-search:SCREEN-VALUE, NO-LOCK ) NO-ERROR.
IF lOkay THEN
REPOSITION browse-name TO RECID hBuffer:RECID NO-ERROR.
/* Don't forget to cleanup properly ... */
DELETE OBJECT hBuffer.
ASSIGN hBuffer = ?.
</code-snipet>
 
RealHeavyDude said:
You should look at dynamic programming, somthing like this:

<code-snipet>
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.
ASSIGN cTableName = "Customer"
cFieldName = browse-name:CURRENT-COLUMN:NAME.
CREATE BUFFER hBuffer FOR TABLE cTableName.
ASSIGN lOkay = hBuffer:FIND-FIRST ( "WHERE " + cFieldName + " BEGINS " + f-search:SCREEN-VALUE, NO-LOCK ) NO-ERROR.
IF lOkay THEN
REPOSITION browse-name TO RECID hBuffer:RECID NO-ERROR.
/* Don't forget to cleanup properly ... */
DELETE OBJECT hBuffer.
ASSIGN hBuffer = ?.
</code-snipet>
Click to expand...

Your code results an error.
Unable to understand after -- "Assign lOkay". (247)
 
My fault.

I've written code like this so many times that I didn't test my own advice. You are correct the code is missing the enclosing quotes or double quotes for the string - because whatever is contained in the SCREEN-VALUE of f-search will not resolve into a variable name ...


ASSIGN lOkay = hBuffer:FIND-FIRST ( 'WHERE ' + cFieldName + ' BEGINS "' + f-search:SCREEN-VALUE + '"', NO-LOCK ) NO-ERROR.

Pls bear with me, RealHeavyDude.
 
RealHeavyDude said:
My fault.

I've written code like this so many times that I didn't test my own advice. You are correct the code is missing the enclosing quotes or double quotes for the string - because whatever is contained in the SCREEN-VALUE of f-search will not resolve into a variable name ...


ASSIGN lOkay = hBuffer:FIND-FIRST ( 'WHERE ' + cFieldName + ' BEGINS "' + f-search:SCREEN-VALUE + '"', NO-LOCK ) NO-ERROR.

Pls bear with me, RealHeavyDude.
Click to expand...

I think the syntax is still incorrect sir, i have an error like below.
---------------------------
Error
---------------------------
Unknown attribute FIND-FIRST used in widget:attribute phrase. (3406)
** Could not understand line . (196)
---------------------------
OK
---------------------------

Thanks
 
Ancient, obsolete and unsupported versions of Progress like 9.1C do not have the FIND-FIRST method.

You would need to do a GET instead.
 
Thanks Tom.

As I am working on OE10 since at least 4 years I didn't remember that the FIND-FIRST () method on the buffer object handle not available in 9.1c. Don't know exactly when it was introduced.

Best regards,
RealHeavyDude.
 
Everything you code statically will be resolved at compile time. There is only one way to change the query where at run time: Use dynamic objects - or at least grab the handle of a static query object and use that.

If you don't know how to use dynamic objects or object handles then I think this is beyond the scope of this forum and you should get a training. BTW, dynamic objects and the ability to change them at runtime is one of the big features that were introduced with Progress V9.

Regards, RealHeavyDude.
 
You must log in or register to reply here.
Back
Top