next »

[.]

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

[Sebastien F.]

Hello,

I guess you are not using an Informix database - right?
I have tested this with Informix 11 and MATCHES 'A100' returns a row...

Understand that MATCHES is Informix specific, the standard is the LIKE operator.

If it's not and Informix server, the ODI driver converts the MATCHES expression to a LIKE expression, because MATCHES does not exist on other databases. You can see this by setting FGLSQLDEBUG=3.
Since non-Informix databases have different blank padding semantics in CHAR/VARCHAR comparisons, so it's normal that you don't get the row.

To workaround this difference, you must use an equal operator instead of MATCHES when the search string does not contains * or ? characters.

However, I strongly recommend you to use a CONSTRUCT statement instead of PROMPT.

    https://4js.com/techdocs/genero/fgl/devel/DocRoot/User/Construct.html

According to the database type, CONSTRUCT generates a LIKE expression when needed (user still enters * or ? wildcard chars).

Cheers,
Seb

[.]

Thanks Seb,

 Yes I am using an infomix database, I am using  Genero db

[.]

Sorry, I meant to say I am NOT using an informix database but Genero db