1

I have a ADODataSet where the "Filtered" property is set to True (Filtered:= True;)

When I apply the filter:

[No] like '2%'

an error "Filter can't be opened" pops up. [No] is a field in the ADODataSet of integer type. When I apply a similiar filter to string columns it works fine.

e.g:

[LastName] like 'Jo%'.

Any idea ?

Thanks.

Pejman Nikram
  • 570
  • 2
  • 11
  • 26
  • 5
    What would you expect an integer `LIKE 2` to be? Integers are numerics, so you work with them as numbers. When you're looking for an integer value between 20 and 29, you don't look for an integer `LIKE 2`, you look for a number between 20 and 29. `LIKE` is for character (string) fields and nothing else, as @TLama says. – Ken White Sep 05 '12 at 22:28
  • Is there functions to convert int to string in msSQL? In Firebird server i could do *CAST(No as VarChar(10)) like '2%'* or maybe *(' ' || No) like ' 2%'*, something like that. – Arioch 'The Sep 06 '12 at 09:00

1 Answers1

1

I agree with Ken, if you're looking for numeric values 20 through 29 or 200 through 299, then search based on the values. If you still want to do as you ask, consider that the Filter property of a tAdoDataSet is not identical to adding a "where" clause to your query. A where-clause would be dealt with on the server side, using the server's syntax. The Filter property, on the other hand, is parsed within your software and has its own syntax rules.

Option one is to use an actual where-clause. In my test I'm using MS SQL Server. I changed the SQL text to:

select [LASTNAME], [NO] from PEOPLE
where [NO] like '2%'

In this case, the syntax rules for MSSQL will cast the numeric value to a character string before applying the filter.

Option two is to alter the query to return a string. 

select [LASTNAME], [NO],
cast ( [NO] as varchar(20) ) as [NO_AS_CHAR] 
from PEOPLE

And then change the filter to 

[NO_AS_CHAR] like '2%'
David Dubois
  • 3,842
  • 3
  • 18
  • 36
  • 1
    Neither of those options makes any sense. As I explained before, there's no such thing as a numeric (integer) value `LIKE 2%`, because that's not how you handle numbers. Casting/converting it to a string to make an invalid nonsense selection can't be the right solution, unless someone used a totally wrong data format for the column. (In which case, the proper answer is "change the column type to the right one, and if that isn't an option then here's a bad workaround"). I'm not downvoting, but I can't update an answer based on logic that makes no sense in the first place. :-) – Ken White Sep 06 '12 at 11:00
  • 1
    As I stated, Ken, I agree with you. From the context of the original post, I agree with your guess that column [NO] holds an identifier, such as an employee number. It's not a numeric value, i.e. having an employee number of "2173" doesn't mean there are two-thousand seventy-three of something. Whoever designed the database schema used a numeric type, perhaps because they didn't understand the distinction, or perhaps they needed a smaller datatype for performance. I agree that changing the data type would be ideal, but perhaps this is not an available solution to the poster. – David Dubois Sep 06 '12 at 11:53
  • 1
    :-) You misunderstood. My objection was that you didn't suggest changing the datatype at all, which of course would have been the preferred fix, and *then* provide the alternative of the conversion as a workaround. However, I don't think the conversion is right in this case, either; if it's a 4 digit employee ID, the better query would be `WHERE [No] BETWEEN 2000 AND 2999`, which is proper use of the data and avoids the conversion totally. :-) – Ken White Sep 06 '12 at 12:56