Like with Integers

Like with Integers

  

Does anyone have a simple workaround for the filtering error produced when attempting to use a like operator with a field that is an integer?

SyntaxEditor Code Snippet

Mem.MemberNum like "%" + Session.Mems_Search_MemNum + "%"

Thanks!

Solution

Hi Barb,

It depends on what you want. If MemberNum is an integer, and you want the user to be able to search on a part of that number (e.g. MemberNum is 12345678 and it must match a search on 456), you need to convert the number to text first, like this:

IntegerToText(Mem.MemberNum) like "%" + Session.Mems_Search_MemNum + "%"

Note however that this is not very fast (but then again, if MemberNum was a Text attribute, it wouldn't be much faster).


Solution

Thanks,Kilian, that worked great - hoping no performance issues to deal with later!

Barb

Great to hear Barb. And yeah, full text searches are always looming performance problems, but if the data set is relatively small they're ok.

I had the same problem with integer data types and the given solution does not apply my case. If you have a search criteria as session variable and you'd need to filter a column of type integer by the search criteria and the search is not made by 'Contains a string', but with plain value (eg. source table would have entries like 1,2,3,...10 and in your search criteria you would have nothing (all values are accepted) or any single value of those 1,...10). In the standard training material for Movie database using the Genre filter, this is how it works as filter in aggregate widget:

SyntaxEditor Code Snippet

Session.MovieGenreFilter = NullIdentifier() or Movie.GenreId = Session.MovieGenreFilter

but you can't use such syntax if you have SQL widget. Don't you.

So how to make the 'like' kind on select in SQL widget in this case?

I was planning to have IF-widget in the flow, and depending if the search value is empty or not I'd have 2 different SQL-widgets (one that ignores search criteria from the select...where statement and second that uses the search value as such (...where ... and myfield = session.searchcriteriavalue ).

(I am still trying to implement this on the HotelBooking training exercise...)

Hi Kimmo,

It seems you have a different question than the original poster, and this topic is already marked as "solved", so please in the future create a new post in these circumstances.

That said, I'm not sure I understand what you mean. The Aggregate Filter you show has nothing to do with a "LIKE", it's a straightforward Filter in case the user can filter on a specific value or not filter at all. Using the SQL Statement, you would pass the Session.MovieGenreFilter as an Input Variable, and in the WHERE clause do something like this:

(@MovieGenreFilter = 0 or {Movie}.[GenreId] = @MovieGenreFilter)

So you can see that it translates pretty much 1:1 from an Aggregate Filter.

The next question of course, is why would you want to use SQL instead of an Aggregate?

why to use SQL? I have no proper argument on that, but well, for example in hotelbooking training exercise there is this proposed task:

The GetAvailableRoom Action, associated with the respective Button, tries to get the cheapest available Room, for the Booking information added to the Form. To get the Room, you can add a SQL Query statement to the Action, with the following SQL:
SELECT {Room}.* FROM {Room} WHERE @NumberOfAdults > 0
AND {Room}.[AdultsCapacity] >= @NumberOfAdults
AND {Room}.[AdultsCapacity]+{Room}.[ChildrenCapacity]>=@NumberOfAdults+@NumberOfChildren
AND NOT EXISTS
(SELECT 1 FROM {Booking} WHERE {Booking}.[RoomId] = {Room}.[Id]
AND (@CheckInDate BETWEEN {Booking}.[CheckInDate] AND {Booking}.[CheckOutDate] - 1 OR @CheckOutDate BETWEEN {Booking}.[CheckInDate] + 1 AND {Booking}.[CheckOutDate])
AND {Booking}.[StatusId] <> @CanceledStatus)
ORDER BY {Room}.[Price] ASC

With above example, Although not relevant, but you could apply a your search criteria which was set in bookingDetail screen in the where clause of  GetAvailableRoom Action: ... AND ( {Booking}.[StatusId] <> @StatusSearchId) or @StatusSearchId = NullIdentifier() ).

In my case I am going to have filter in the Aggregate. I mean The Hotelbooking exercise to filter bookings with statusId.

In general I am a newbie with Outsystems, and found it odd, that having a variable reference in SQL statement's Where part but not even referring to any database table elements ( "...or @StatusSearchId = NullIdentifier() ).


Hi Kimmo,

In SQL (the language) you can refer to variables just fine, that has little to do with OutSystems. Note that inside an SQL Statement, there's no such thing as NullIdentifier(). If you want to test whether a variable is empty, you test against 0, when you want to test whether an Id from an Entity is empty, you test with IS NULL.

I thought too that referring to NullIdentifier() is not permitted in SQL, but now you confirmed it. I guess comparing to value 0 applies only if data type of variable is integer or decimal?

Yes indeed, for Text Identifiers use to single quotes instead of 0.