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!
and then the code form my little procedure editor snippet that does return expected result...
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!
Code:
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...
Code:
for each grades where
string(grades.tf-allow,">>>>>9.99") matches "*1~~.*":u no-lock
break by tf-allow:
display grades.tf-allow.
end.