To continue with this content, please log in with your Data Access ID or create a new account.
Cancel Data Access ID
You may not be authorized to see this content. Please contact Data Access Europe for more information.
Cancel Data Access Europe
You are not authorized to see this content.
Cancel Data Access Europe
Next lesson:
Embedded SQL using the freetexttable function

DataFlex – SQL Search

Lesson 2 – Using the freetexttable function

The results in the previous lesson didn’t get sorted on relevance, they were just sorted randomly. You probably want the most relevant result on top, especially when using multiple keywords.

The FREETEXT function only gives true or false, but to search on relevance you also need a relevance rating so then you can use the FREETEXTTABLE function. The FREETEXTTABLE function returns a table. This table has a key which is your primary key of the record that is being found. It also returns a rank which is just the relevance rating. So a number, and how higher the number the more relevant the result is. Use this in the ‘FROM’ class of your query and join in the actual data to show results that make sense. In this lesson we’re going to look at a sample query that shows this.

  1. Back in the SQL management studio you can see the sample query
  2. Select everything from the FREETEXTTABLE and execute this query
  3. Executing this query will provide you with a key which is the primary key of the table that it found, so the track table and the relevance rating/ranking.

  4. The first hit has a high ranking at 142, while the other hits have smaller rankings, so they are less accurate.
  5. But this information isn’t really usable yet, so what you would normally do is to join in the data that you actually want to show.
  6. Do this by using a left join, where you tell it you want every record that’s in the result set, and that we want to join in the actual record with the data. Execute it again.

  7. Now you see  the TrackId, the name, the composer and the album. All the information you need and also the search ranking.

  8. This is how you can show your results properly in the application.