I'm trying to select off a column having the characters RF in it. I always get 'No rows selected':

select user, name, shcm
from ttaad200
where shcm like '%RF%'

The characters are always in the same positions so if a substring options works that works for me.

If the positions are fixed you can try this -


select user, name, shcm
from ttaad200
where shcm(start.position;end.position) = "RF"

You should use the same syntax as in the expr.compile (http://www.baanboard.com/programmers_manual_baanerp_help_functions_expressions_runtime_expr_compile) function.

Use .* where you mean 0+ characters. Use only the period for a single instance. So, if you want RF as the second and third characters, you would use:


where shcm like ".*RF.*"

The one the NPRao has suggested will work fine but please make sure that there is infact data matching the query.

Maxime.

I got the field(start;end) to work but the like didn't. Thanks for all your help.

Odd that the like doesn't work for you. Both constructs work fine for me. Oh, well...

More info -

SQL optimizations
BAAN Application Performance Guide
27
3.7 Using like
Problem
The like condition is evaluated in the driver and not in the RDBMS.
So, all selected records are copied from RDBMS to driver before checking the like
condition.
Solution
Replace the like function by tests on substrings if possible.
Example
Bad situation
Select books.*
from books
where books.number inrange 1 and 10
and books.author like "P*"
Improved
Select books.*
from books
where books.number inrange 1 and 10
and books.author(1;1) = "P"

Large performance impact

3.6 As set with n rows. This select option limits the number of rows returned from the select. For a 15,000 row table, the select took 5,000 milliseconds without this option and 1 millisecond with as set with 1 rows, only returning a single row.

3.7 The like condition. This condition is not applied until the data is in the database driver so more data is selected than is needed. If a substring is used, the data is filtered at the dbms so only the required rows are returned. On a 15,000 row table, the like condition took 4800 milliseconds and the substring took 290 milliseconds.

Sections 3.3, 3.4, 3.5 and 3.10 discuss situations that affect index selection for a select statement. The choice of wrong indexes is often the single largest performance impact in an application. For simple queries, these sections give examples of how to influence index selection. However, for complex queries, it is sometimes very difficult to predict which index will be chosen, especially if you don’t know the details of the dbms optimizer. In these cases, a dbms monitoring and tuning tool is essential.