Matches operator and decimal fields

[stokefc22]

Hi All, I hope some can help me with this before i pull all my hair out!!

long story as short as possible...
I have a dynamic tt that I populate using a given table I then display the data in a browse and allow the user to click a series of column headings to create a string of fields to search on. I have a fill in where on value change i generate a query to find records where the value of the selected matches the value in the fill in. In order to achieve this I have to convert each field I'm searching on into a character field(maybe not the best practice but there you go..) . The problem arises when I search on a decimal field. If I enter "1." then I get all "1.xxxx" and all "1xxxx" as the point is wildcard character in the matches. I have tried the double tilde with no joy, I can do this happily in a procedure editor but not using a query-prepare statement. Below is my code, any help would be much appreciated!

    define variable chrQuery            as character  no-undo.  
    define variable intCount            as integer    no-undo.  
    define variable hanTempTablePointer as handle     no-undo.  
    define variable hanCol              as handle     no-undo.  
    define variable chrFormat           as character  no-undo.  
    define variable chrFieldName        as character  no-undo.
    define variable chrSearchText       as character  no-undo.

    /* search fields*/
    assign chrSearchCriteria    = chrSearchCriteria:screen-value in frame {&frame-name}.  
  
    if chrSearchFields = "":u then  
    do:  
        message "Please select at least one field to search on.":u  
            view-as alert-box info buttons ok.  
        return "error":u.  
    end.  

    /* add double tilde*/
    if index(chrSearchCriteria,".":u) > 0 then
        assign chrSearchCriteria =  substring(chrSearchCriteria,1,index(chrSearchCriteria,".":u) - 1) + "~~" + substring(chrSearchCriteria,index(chrSearchCriteria,".":u)).

    assign  hanTempTablePointer = hanTempTable:default-buffer-handle  
            chrQuery            = "for each ttTable":u.  
  
    do intCount = 1 to num-entries(chrSearchFields):  
        if intCount = 1 then  
            assign chrQuery = chrQuery + " where ":u.  
       
        assign chrSearchText = entry(intCount,chrSearchFields).

        if index(chrSearchText,"[":u) > 0 then
            assign chrFieldName = substring(chrSearchText,1,index(chrSearchText,"[":u) - 1).
        else
            assign chrFieldName = chrSearchText.

        assign  hanCol      = hanTempTablePointer:buffer-field(chrFieldName)  
                chrFormat   = if hanCol:data-type = "date":u then ",":u + quoter("99/99/9999":u) else ",":u + quoter(string(hanCol:format)) 
                chrQuery    = chrQuery + "string(ttTable.":u + chrSearchText + chrFormat + ") matches ":u + quoter("*":u + chrSearchCriteria + "*":u).                  
  
        if intCount <> num-entries(chrSearchFields) then  
            assign chrQuery = chrQuery + " or ":u.  
    end.  
  
    assign chrQuery = chrQuery + " no-lock":u.  

    /* this simply adds any break bys and reopens query...*/
    run displayData(hanTempTablePointer,chrQuery).

and then the code form my little procedure editor snippet that does return expected result...

for each grades where
        string(grades.tf-allow,">>>>>9.99") matches "*1~~.*":u no-lock
        break by tf-allow:
    display grades.tf-allow.
end.

 

[TomBascom]

Are you sure that you want to be doing this? MATCHES means a table scan. There is no way to bracket a query that uses MATCHES.

Looking at your code you also seem to have fallen into the composite field trap. It looks like a "1" in the hundreds place has magical powers. This is a major violation of relational database design which will lead to any number of horrible coding problems like this one.

 

[GregTomkins]

MATCHES would be OK if the result set is small enough, which it might be given the context is a browse.

Anyway, to the question at hand, I tried it and it worked fine for me. I think maybe there is a bug in your STRING code somewhere. I did this (below) and it correctly retrieved a value of 20.00 vs. 2.00. I don't think the use of CHR vs. escaped literals is actually relevant.

def var m as c.
m = quoter("*2" + chr(126) + chr(126) + chr(46) + "*").


h_qh:QUERY-PREPARE("FOR EACH " + p_table +
" WHERE string(cash,~">>>>>.99~") MATCHES " + m).

It would be disconcerting if the claim in your original question were true, because it would imply that PSC's MATCHES implementation is different depending whether it's used in a QUERY-PREPARE vs. directly, and that would be seem pretty shoddy. Good thing it's probably not true.

 

[Stefan]

The problem arises when I search on a decimal field. If I enter "1." then I get all "1.xxxx" and all "1xxxx" as the point is wildcard character in the matches. I have tried the double tilde with no joy, I can do this happily in a procedure editor but not using a query-prepare statement. Below is my code, any help would be much appreciated!

You need to double escape the tildes.

DEFINE TEMP-TABLE tt FIELD de AS DECIMAL.


CREATE tt. tt.de = 1.1.           
CREATE tt. tt.de = 11.


DEF VAR hq AS HANDLE.


CREATE QUERY hq.
hq:SET-BUFFERS( TEMP-TABLE tt:DEFAULT-BUFFER-HANDLE ).
hq:QUERY-PREPARE( "FOR EACH tt WHERE STRING( de ) MATCHES '1~~~~.*'" ).
hq:QUERY-OPEN().


DO WHILE hq:GET-NEXT():
   MESSAGE tt.de VIEW-AS ALERT-BOX.
END.

Also beware that if you should be matching a decimal point depends on your numeric settings, so my example should really contain:

hq:QUERY-PREPARE( SUBSTITUTE( "FOR EACH tt WHERE STRING( de ) MATCHES '1&1*'", IF SESSION:NUMERIC-DECIMAL-POINT = "." THEN "~~~~." ELSE SESSION:NUMERIC-DECIMAL-POINT ) ).

 

[stokefc22]

Hi All, thanks for all your input I finally cracked it!! In an ideal world I agree we wouldn't do this but unfortunately I do not live in one :-(.

I ended up going with Stefan's example with a slight tweak.

hq:QUERY-PREPARE( "FOR EACH tt WHERE trim(STRING( de,'>>>,>>9.99' )) MATCHES '1~~~~.*'" ).

I needed the bit about the format as converting the decimal field to a string stripped off the '.00' from '1.00' and the bit about the trim to allow a format that was big enough for the largest value but also small enough for the smallest value.

Once again, many thanks.