Title: Sql Matches clause Post by: . on March 31, 2008, 08:40:42 am 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 Title: Re: Sql Matches clause Post by: Sebastien F. on March 31, 2008, 09:43:24 am 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 Title: Re: Sql Matches clause Post by: . on March 31, 2008, 11:41:03 am Thanks Seb, Yes I am using an infomix database, I am using Genero db Title: Re: Sql Matches clause Post by: . on March 31, 2008, 11:52:08 am Sorry, I meant to say I am NOT using an informix database but Genero db |