I try to do something like this:


bpid = "C0221.*"
select tccom100.*
from tccom100
where tccom100.bpid like :bpid
selectdo
endselect


I want to find all the partner that begins with some caracters (this is variable).

How can I do that. The before code doesn´t compile.

Thank you in advance.

Monica,

You can use the 'wherebind' clause for the query. Refer to - Embedded SQL (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_embedded_sql)

I try it but or I don't know how to do it or it doen´t work. I wrote this:


select tccom100.*
from tccom100
where tccom100.bpid like :1
wherebind(1, bpid)
selectdo
...........
endselect


When I compile this it says that bpid is not expected

Here is a sample code, you can avoid the wherebind option.

table tttadv200
function main()
{
string name(4)
name = "2500"
if spool.open("", "", 1) then
select ttadv200.cpac, ttadv200.cmod, ttadv200.cses
from ttadv200
where ttadv200.cses(1;4) = :name
selectdo
spool.pr.line = ttadv200.cpac & ttadv200.cmod & ttadv200.cses
spool.line()
selecteos
spool.close()
endselect
endif
}

Another option is to use the 'alike' (not sure if its available in Baan-5 version)

select ttaad200.user
from ttaad200
where ttaad200.user alike "%bsp%"
and ttaad200._compnr = 000
selectdo
|* message("%s", ttaad200.user)
endselect

Try this....
select tccom100.*
from tccom100
where tccom100.bpid(1;3) like :bpid
selectdo
endselect

bpid take as a input
and that should be a string field.

Thanks,
Dheerendra

Dheerendra,

Refer to the Design Principles help file -
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.
Secondly, it is good to know that the like condition cannot evaluate Multi Byte fields.
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"

I belive that the best solutios is to use dynamic sql. It works fine
Creo que para esto , lo mejor es usar sql dinámico. Funciona muy bien.