Would like to be able to search/print Item Table by description based on a variable entered on the form. Tried using:

select tiitm001.*
from tiitm001
where tiitm001.dsca like :itemdesc

But get an error saying ":itemdesc" not expected.

Looks like there might be a way using "expr.compile". Does anyone know how to do - have an example? Thanks.
:confused:

From the manual I found -

LIKE operator

The LIKE operator compares operand1 with operand2. Operand2 can contain wild cards. For example:

WHERE name LIKE "[Tt]riton.*"

In this example, "Triton2.1" and "triton" are legal values. In this case, operand2 is a regular expression. See the expr.compile() function for a list of the possible features that the expression can contain.

I think you need to use the dynamic sql to use the like operator, if I can find that code, I will post it up here for your reference.

Are you trying to do an SQL query? If you are then here is the information I have in the manual:

. means any character
* take the previous characters from 0 or more times
[] means one of the characters between []
example [abcd23] or [a-z]
[^] means any character except the character
example [^XYZ0-9]
"" meas a double quote within a string

The string after like needs to be in quotes
example
where tisfc001.orno like "12.*"

such as:


where itm001.dsca like :itemdesc

(itemdesc is provided from input on the form)

Thanks for your replies.

you cannot use like in combination with a variable. Perhaps you can use a string concatenation with sprintf$


where tiitm001.item like ""&sprintf$("""%s""", itemdesc)

Have you tried using dynamic sql, where you essentially construct your query as a string, run it through sql.parse, and then do one or more sql.fetch() commands to retrieve data? Since you can create your sql statement as a string before executing the query, that should work. It also has an sql.where.bind which may allow you to reuse it without doing multiple sql.parse() commands.

Here's a sample that you guys gave me about two years ago. I need to count how many are 'like' my variable 'spcl.ref1' and get the company. My query includes my current company, 'company', and my whse company 'whse.comp'.

|*dmw 120500 new code added to count with wildcard

long sql_id
sql_id = sql.parse("select tdsls950.* from tdsls950 "
& " where (tdsls950._compnr = :company or "
& " tdsls950._compnr = :whse.comp) "
& " and tdsls950.a.cref1 like "
& """.*" & spcl.ref1 & ".*""")

sql.exec(sql_id)

while not sql.fetch( sql_id)
count = count + 1
check.comp = tdsls950._compnr
endwhile
if count = 0 then
tdsls950.a.cref1 = ""
ref.not.found = tcyesno.yes
endif

sql.close(sql_id)
|*dmw 120500