Question For Each Where Clause Wildcards

[jamie_moore]

Hi,

I'm trying to use the wildcard "*" with the variable cReasonCode so that in the Where clause when you enter "*" it matches all records. Currently it's literally searching for the character "*" and not treating it as a wildcard. Any help would be greatly appreciated.

DEFINE VARIABLE iCompany AS INTEGER FORMAT ">>" NO-UNDO LABEL "Company"

INIT "10".​

DEFINE VARIABLE iDepot AS INTEGER FORMAT ">>" NO-UNDO LABEL "Depot"

INIT "2".​

DEFINE VARIABLE daStartDate AS DATE NO-UNDO LABEL "Start Date"

INIT " / / ".​

DEFINE VARIABLE daEndDate AS DATE NO-UNDO LABEL "End Date"

INIT " / / ".​

DEFINE VARIABLE cAdjustType AS CHARACTER NO-UNDO LABEL "Adjust Type"

INIT "*".​

DEFINE VARIABLE cReasonCode AS CHARACTER FORMAT "x(3)" NO-UNDO LABEL "Reason
Code"

INIT "*".​

DEFINE VARIABLE deThreshold AS DECIMAL FORMAT ">>9.99" NO-UNDO LABEL "Threshold"

INIT "0.00".​

/* Prompt for user input */
UPDATE iCompany HELP "10=HSC, 20=Wardles"
iDepot HELP "Enter Depot"
daStartDate HELP "Start date(?=date will be set at runtime)"
daEndDate HELP "End date(?=date will be set at runtime)"
cAdjustType HELP "All=*, Receipt=skrc*, Issue=skis*, Adjust=skaj*"
cReasonCode HELP "Enter Reason Code of '*' for all"
deThreshold HELP "Enter value on wich to exclude adjustments"
WITH 1 COLUMNS FRAME USER-PROMPT TITLE "STOCK ADJUSTMENT REPORT" CENTERED
ROW FRAME-ROW(USER-PROMPT) + 8.

OUTPUT TO "/upgrade/Jamie-SKR520.txt" LANDSCAPE.
FOR EACH stkadj-reas NO-LOCK WHERE company = iCompany AND reason-code =
cReasonCode:
DISPLAY reason-code reason-desc.
END.
OUTPUT CLOSE.

 

[Fabio]

Hi,

Please read this, it can solve your doubt: OpenEdge 11.6 Documentation

Pay attention, because MATCHES does not use index. If "stkadj-reas" table is bigger, your FOR EACH may perform slowly.

Or if "MATCHES" does not work for you, do this:

IF cReasonCode = "*" THEN DO:

  FOR EACH stkadj-reas NO-LOCK WHERE company = iCompany:
    DISPLAY reason-code reason-desc.
  END.

END.
ELSE DO:

  FOR EACH stkadj-reas NO-LOCK WHERE company = iCompany AND reason-code = cReasonCode:
    DISPLAY reason-code reason-desc.
  END.

END.

Regards,

 

[jamie_moore]

Thank you! That has worked perfectly

 

[Cringer]

See if a BEGINS will be a compromise because BEGINS will use an index if it can. As Fabio says, matches will not, and it could become very slow.

 

[jamie_moore]

Thanks Cringer that has helped things

 

[tamhas]

For situations like this, my inclination is to test the values the user has supplied and construct a where clause dynamically based on what I find there. Thus, if the user supplies "*", simply omit that field from the where clause altogether. Not only does this allow making much more efficient where clauses, because you are not testing things which will ever be anything but true, but it allows strategies to optimize for different indexes. For example, if you have one index which begins with the company field and another that begins with the date field, if the company is *, then specify the where so that the date index will be selected. And, vice versa, if the dates are the default limits, construct to select on company or whatever is appropriate. Similarly, if the start date is default and an end date is supplied, then include only the end date in the where.