Hi all,

Is it possible to construct a select to find all values in a table where a certain string-field contains a certain sub-string? Similar to:
srch.name = shiftl$(strip$(srch.name)) & ".*"

select tccom980.name
from tccom980
where tccom980.name like :srch.name
order by tccom980._index2 | index2 = name...
selectdo
do.found.name(tccom980.name)
selectempty
message("Not found")
endselect

Yes, it is. You must, however, construct a proper serch string with wildcards, that are not as simple as in "*.*". You can find them in expr.compile manual:
http://www.baanboard.com/programmers_manual_baanerp_help_functions_expressions_runtime_expr_compile

Great. I already found that.

Does anybody actually have a working example of this to help a guy out?

Here you are:

select tccom001.* from tccom001
where tccom001.nama like ".*[Aa][Tt][Ww].*"
selectdo....

searches for all employees with "atw" string anywhere in first name, case insensitive.

Grzegorz,

Thanks for the input - your example is OK for a fixed string, but won't work for a VARIABLE, as in my example.

My user enters a short string into a dialog field, and then the table is searched for any entries LIKE this.string. Is this possible?

Nope, query with variable after like clause won't compile.

As I suspected - where else in the world is one likely to find a flavour of SQL that is quite as USELESS as Baan "SQL"? It should be called Baan SQUAT - as in diddley.

Thanks anyway...

Wouldn't it work if you use the Dynamic SQL ?
http://www.baanboard.com/programmers_manual_baanerp_help_functions_dynamic_sql_queries_overview

- George

Example:
long sql , retval
string mysearch(30)

mysearch = """.*[Aa][Tt][Ww]*."""

sql = sql.parse("select tccom001.nama "&
"from tccom001 "&
"where tccom001.nama like " & mysearch &
" and tccom001._compnr = 550 ")

sql.exec(sql)

if sql.fetch(sql) = 0 then
message(tccom001.nama)
endif
I hope it helps,

- George

Yep! that'll do it... Here's the code:
long found.count, expr.id, sql.id, str.len, ii
string this.many(10), like.str(50), this.name(50), sql.str(200), srch.str(100), this.char(1)

this.name = ""

srch.name = toupper$(shiftl$(strip$(srch.name)))
str.len = len(srch.name)
srch.str = ""

| Form a string like '[Aa][Bb][Cc]'.....
for ii = 1 to str.len
this.char = srch.name(ii; 1)
srch.str = srch.str & "[" & toupper$(this.char) & tolower$(this.char) & "]"
endfor

| Add in the quotes and wild-cards to form a string like '".*[Aa][Bb][Cc].*"'
like.str = """.*" & srch.str & ".*"""
found.count = 0

sql.str = " select tccom980.name :1 " &
" from tccom980 " &
" where tccom980.name like " & like.str & " " &
" order by tccom980._index2 "

if ( not sql.id ) then
sql.id = sql.parse(sql.str)
endif

sql.select.bind(sql.id, 1, this.name)
sql.exec(sql.id)

while ( true )
on case ( sql.fetch(sql.id) )
case eendfile:
break
case 0:
found.count = found.count + 1

| Print.........

continue
default: | error
message("Error %d occurred", db.error())
message("Found no instances of " & chr$(34) & srch.name & chr$(34))
endcase
break
endwhile

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

Thanks for your help. I don't do an awful lot of programming in Baan these days (thank heaven!) - I much prefer playing with real programming languages where there's no Standard Program waiting in the sidelines to throttle the life out of you... ;-)

Rob