Is there a way to use wild cards or other SQL method on an DBISAM integer field, and a parametrized query.  I am using something like this:

1. This is used on my character field:

QryJobReference.Params[1].AsString := '%' + trim (EditJobReference.Text) + '%';  
// works great as a character field.

SELECT * FROM JobOrders WHERE Custcode = :UCustode and Reference  = :UJobRefDescription




2. This statement works. I would like to add a wild card or other SQL method with use of an Integer field somehow.

QryJobReference.Params[0].Asinteger := StrtoInt(EditJobNumber.Text);
// This is used on my Integer field and works for exact matches

SELECT * FROM JobOrders WHERE Custcode = :UCustode and ReferenceNo  = :UJobRefNumber

I get a DBISAM 11949 parsing error if I try to apply any wildcards,  or if I use LIKE in my SQL statement on integer fields.


John Postnikoff

John


I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg

SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10))  = '%'+:UJobRefNumber+'%'

don't know if it will work or not since I haven't tested it.

However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN

Roy Lambert [Team Elevate]

To answer your question why anyone would want that. In this case the customer has a very long number to search
e.g. 999001054. What I would really like to accomplish is to be able to search a likeness to the series of the last four or five numbers.  Now that I think about it, a trimmed string var would likely be the way to go. Thanks for shedding some light to the matter.

John Postnikoff.

Roy Lambert wrote:

John


I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg

SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10))  = '%'+:UJobRefNumber+'%'

don't know if it will work or not since I haven't tested it.

However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN

Roy Lambert [Team Elevate]

To answer your question why anyone would want that. In this case the customer has a very long number to search
e.g. 999001054. What I would really like to accomplish is to be able to search a likeness to the series of the last four or five numbers.  Now that I think about it, a trimmed string var would likely be the way to go. Thanks for shedding some light to the matter.

John Postnikoff.

Roy Lambert wrote:

John


I think it can be done but I'm not sure why anyone would. The approach would be to use CAST to convert the integer into a varchar eg

SELECT * FROM JobOrders WHERE Custcode = :UCustode and CAST(ReferenceNo AS VARCHAR(10))  = '%'+:UJobRefNumber+'%'

don't know if it will work or not since I haven't tested it.

However, unless its a structured number then I don't see the point. You'd be better off using the operators that go with integers >, < etc or BETWEEN

Roy Lambert [Team Elevate]

John


I may be teaching granny to suck eggs here but 999001054 looks like a number with built in significance (eg 999 could be a style number or product range) rather than a simple series. If you have the option it might be worth looking at splitting it up for storage purposes which would make it easier for searching.

Roy Lambert