Hi,
We have a customer enquiry program that we want users to enter customer details as search criteria.
If we have two records with customer codes A100 and A101. Entering A* as customer code search criteria and executing something like:
Select * from customers where cu_code matches 'A*'
will retrieve both records, as expected.
The problem comes when you enter the customer code in full as A100, (select * from customers where cu_code matches 'A100'), the query will not retrieve this record.
But entering the full code and asterick at the end (A100*) will in fact retrieve the record.
This is happening for all the fields that we use to construct the sql stament.
Shouldn't entering cu_code in full return that particular record ?
Here is my test case that uses a temp table
database test
####################################################
#
# program to highlight problem with matches in sql
####################################################
globals
define
mv_sel string, # select string
mr_customer record
cu_code char(10), # customer code
cu_name char(20), # customer name
cu_city char(20), # city address
cu_country char(20), # county address
cu_bank char(20) # customers bank
end record
end globals
###############################################
#
# main function
###############################################
main
#create tempt table
call f_temptable()
let mr_customer.cu_code = "A100"
let mr_customer. cu_name = "custA"
let mr_customer.cu_city = "cityA"
let mr_customer. cu_country = "countryA"
let mr_customer.cu_bank = "A100"
insert into t_customers
values ( mr_customer.*)
let mr_customer.cu_code = "A101"
let mr_customer. cu_name = "custA1"
let mr_customer.cu_city = "cityA1"
let mr_customer. cu_country = "countryA1"
let mr_customer.cu_bank = "bankB"
insert into t_customers
values ( mr_customer.*)
let mr_customer.cu_code = "B100"
let mr_customer. cu_name = "custB"
let mr_customer.cu_city = "cityB"
let mr_customer. cu_country = "countryB"
let mr_customer.cu_bank = "bankB"
insert into t_customers
values ( mr_customer.*)
let mr_customer.cu_code = "D100"
let mr_customer. cu_name = "custD"
let mr_customer.cu_city = "cityD"
let mr_customer. cu_country = "countryD"
let mr_customer.cu_bank = "bankD"
insert into t_customers
values ( mr_customer.*)
call f_find()
end main
#####################################################
#
# function find
#
#####################################################
function f_find()
define
lr_customer record
cu_code char(10), # customer code
cu_name char(20), # customer name
cu_city char(20), # city address
cu_country char(20), # county address
cu_bank char(20) # customers bank
end record
#enter search criteria
prompt "cust code " for mr_customer.cu_code
call f_construct() # construct sql statement
prepare cust_prep from mv_sel
declare cust_cur cursor for cust_prep
clear screen
display "executing ", mv_sel
foreach cust_cur into lr_customer.*
display lr_customer.*
end foreach
end function
#######################################
#
# function to construct sql statement
#
#######################################
function f_construct()
let mv_sel = " select * from t_customers ",
" where 1 = 1 "
if mr_customer.cu_code is not null then
let mv_sel = mv_sel clipped, " and cu_code matches '",
mr_customer.cu_code clipped, "'"
end if
end function
###########################################
#
# function to create temp table
#
############################################
function f_temptable()
create temp table t_customers
( cu_code char(10), # customer code
cu_name char(20), # customer name
cu_city char(20), # city address
cu_cnty char(20), # county address
cu_bank char(20) # customers bank
) with no log
end function
