I am surprised i couldnt find a good way to do this (I can do this in SQL query analyser with ease. Dont understand why baan wont add such a basic functionality)

-- ibd001 - item data, mcs062 - product class
select tcibd001.item,tcmcs062.dsca
from tcibd001, tcmcs062
where tcibd001.cpcl = tcmcs001.cpcl
and tcmcs062.dsca like "&chr$(34)&".*"&strip$(user.input)&".*"&chr$(34)

- user.input is a field on a form capturing user input and thats used to get the records. This is part of a front end which allows user to search and retrive data
The above query works, but i want to make it better. Irrespective of user enters the input in upper case or lower case i want the query to return a match

Eg, in tcmcs062 description is "WaveRunner Scopes", current query works if the user Types in "WaveRunner" it will find a match, but if they put in "waverunner", "Waverunner", "WAVERUNNER" it wont hit a match

by the way, if baan allowed the use of functions in where clause it would been easy ie
toupper$(tcmcs062.dsca) like "&chr$(34)&".*"&strip$(topper$(user.input))&".*"&chr$(34)

But it wont! Is there any next best option let me know
(Sujith)

The problem is free form text that you let them input - convert everything to uppercase and it makes it easy. I know easier said than done.

Now what would be easy is to add a field to the table, convert it all to upper case and store it on the table. So one field would be user input - they could enter WaveRunner, WAveRUnner, waverunner, etc. The search field would have WAVERUNNER. Then all you have to do is take there search input and use toupper on it, then plug it into the query.

Don't know if this relates, but in GUI, whenever we are running an Maintain or Display session, it is possible to use the built-in query facility, which I am sure you know about - from the Menu Bar --> Tools --> Query.

This allows to use the LIKE option (among many others). The only catch is the Expression field would need to be specified as Regular Expression for the type of query described - not necessarily a bad thing, just takes a bit more time to get the User into the swing of it. In our case, our users make use of this feature occassionally for most of the standard operators (=, <, > etc.). For LIKE, I have given them a 'cheat sheet' how to search for strings, for example:

To seach for any Item Code ending with SK, they would use:
LIKE .*SK .*

Note the SPACE character after the SK - that is to take care of the SPACES that are padded for character fields in BAAN.

It is not very user friendly to take care of the example where there is mixed case. The above query would become:

LIKE .*[sS][kK] .*

[Of course Item Code is forced to upper case, so this is not a good example, but the concept is that this is one way to do this.]

The feature is quite powerful, and has save me a lot of time doing ad-hoc work for the users.

[As an aside, once you are in the Query form, if you choose the "Using Session" from the Help menu-bar option, it give more (quite decent) explanation about how to use the query form.]

Mark
That thought did pass through my mind, but i would prefer not to add any new table fields to run a query. Also its not just one query, its part of a bigger 'baan search engine' we are building it for users. So its goign to be quite many fields.
Any way thanks for the thought.

Shah,
Thanks for the response, but thats not what i am looking for.
Our users do use Query analyser for variety of different reasons.
Any one eles have any other thoughts?
(Sujith)

before select, if u try to transform user.input= "waverunner" to user.input= "[wW][aA][vV][eE][rR][uU][nN][nN][eE][rR]"

never tried it, but ...

Olivier

Hi,

below a really "quick and dirty" example how this can be done with dynamic sql.
This small test session did a search on field nama (Name 1) in table tccom130 (Addresses) regardless if you fill in the letters in uppercase or lowercase.

|******************************************************************************
|* tccom9910
|* Test
|*
|* 2009-10-06
|******************************************************************************
|* Main table tccom130 Adressen, Form Type 4
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table ttccom130 | Adressen

extern domain tccom.cadr cadr.f fixed
extern domain tccom.cadr cadr.t fixed
extern domain tcnama nama.s

domain tcmcs.str256 search.str
long sql

#define SBO "["
#define SBC "]"

|****************************** program section ********************************


|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.cadr.f:
when.field.changes:
cadr.t = cadr.f

field.nama.s:
check.input:
if isspace(nama.s) then
set.input.error("")
endif

|****************************** function section ******************************

functions:

function read.main.table()
{
search.str = ".*" & strip$(shiftl$(build.search.string())) & ".*"

sql = sql.parse("select tccom130.* " &
"from tccom130 " &
"where tccom130._index1 inrange {:cadr.f} and {:cadr.t} and "&
" tccom130.nama like """&search.str&""" " &
"order by tccom130._index1")
sql.exec(sql)
while true
if sql.fetch(sql) = 0 then
rprt_send()
else
break
endif
endwhile

sql.break(sql)
sql.close(sql)
sql = 0

}

function domain tcmcs.str256 build.search.string()
{
string tmp.str(256)
long i,x

x = 1
for i = 1 to 35
if not isspace(nama.s(i;1)) then
tmp.str(x;4) = SBO &
tolower$(nama.s(i;1)) &
toupper$(nama.s(i;1)) &
SBC
x = x + 4
endif
endfor
return(tmp.str)
}

Regards,
Juergen

Looks like Juergen did get it done. I had a session where I did a lot of finding of descriptions for input fields and wrote this query. Combine the two into a library and you could search any description field passed. Just a thought.

|******************************************************************************
| Function to get a description or data field.
|******************************************************************************
function get.a.description( string field.info(255), | Field Information.
string ref.table(16), | Ref. Table name to check
string ref.table.field(32), | Field to find record.
string ref.table.desc(32)) | Field to select in ref. table
{
string sql(120)
long sql_id, found, expid

sql = " select " & strip$(ref.table.desc)
sql = sql & " from " & strip$(ref.table)
sql = sql & " where " & strip$(ref.table.field) & "=" & chr$(34) & strip$(field.info) & chr$(34)
sql = sql & " as set with 1 rows"
sql_id = sql.parse(sql)
sql.exec(sql_id)
found = sql.fetch(sql_id)
error.bypass = 0
sql.break(sql_id)
sql.close(sql_id)
display(ref.table.desc)
}

Wow folks... Great idea.. !!! Glad that i posted here. I didnt think on those lines!!
Super.. Boxer and Juergen!!
I am going to incorporate it and will let you know.. how it all went.

I made the changes and it worked like a charm.
Cant tell you how useful this place is!!
Thanks all