Hi,
i have a problem with the funcion LIKE, I'm not able to pass a variable.
If i write: select... from... where tdsls040.cons.c LIKE "PIPPO" there's no problem but if i write where tdsls040.cons.c LIKE PIPPO (where PIPPO is a variable) the program return: "Syntax error: PIPPO not expected".

Thank you for any answer.

Hello:

If the variable is not a table field or a constant, e.g., a string "abc", then you need to put a column in front of it, e.g. "... LIKE :pippo".

Yours,
Stephan

Just to correct Stephan, also for a table field you should add the : in front...

In embedded SQL like works only with constants . In order to be able to use the variables , u should use the dynamic query .

Search this forum for various examples on dynamic query .

If i put : in front of my variable the system return the same error: "Syntax error: PIPPO not expected"

In embedded SQL like works only with constants . In order to be able to use the variables , u should use the dynamic query .

Search this forum for various examples on dynamic query .

This thread had the same problem and the solution was dynamic query :
http://www.baanboard.com/baanboard/showthread.php?t=26096

- George

Just to step in, the dynamic query yes for this situation.

But some people have mentioned confusion on when to use the ':' in the where clause.

The ':' means to 'bind' the variable's contents to outside the SQL statement. If the table field should hold the value from outside the select statement, then it should have a ':' in front of it. If it is used within the statement to connect the tables together, it should not have the ':'.

Example:

To read the records in tdsls041 where relates to an already filled value in tdsls040, you would write:


select tdsls041.*
from tdsls041
where tdsls041._index1 = {:tdsls040.orno}

(or use the refers to method - this is just for clarity of the ':')

You want to take the tdsls040 value from before the sql. However, if you have a variable for Order Number (orno.in let's say), and you want to read tdsls040 for that value, AND the related tdsls041 record, then you drop the ':' in the read from 040 to 041, because you want the tdsls040.orno value from WITHIN the sql statement (not BOUND to an external value).


select tdsls040.*, tdsls041.*
from tdsls040, tdsls041
where tdsls040._index1 = {:orno.in} |* bound external orno.in
and tdsls041._index1 = {tdsls040.orno} |* value within the select

Thanks to all. Now the program works correctly.
Bye