Posts tagged range
Retrieving the values for domains in SQL queries in the Oracle RDBMS – months, days, numbers and custom value sets1
It happens quite frequently that in our database applications – in queries or in PL/SQL packages – we need to use the values from more or less standard domains or custom lists of values. For example the list of all months in the year – to show a breakdown list of some measure per month. Or the days of the week, to do something similar. Sometimes we just need a list of numbers in a certain range. And it is quite common to outer join such a collection of domain values with the set of measurements, using the partition outer join feature – to ensure that for each category of measurements, there is an aggregate for each of the values in the domain.
This article demonstrates several examples of standard domains, some SQL tricks for composing domains out of thin air and a number of features to spell out numbers and internationalize days and month names.
Not all has to be black and white in SQL Queries: returning match scores instead of only perfect fits
Recently I sat in on a very interesting presentation on an advanced search and matching engine called Elise. The power of this engine lies in the fact that it does not just query records, returning the set of records that satisfy the search criteria – although it has some pretty advanced search filters and operators, but is able to score all records according to the match criteria. Every record gets a score – which can be zero, meaning that not a single search criteria matched even the smallest way. Higher scores mean better matches on more, or on the most important, criteria.
Many search operations do not necessarily need perfect matches. They need possibly multiple results that are all matches to a certain degree. Google for example returns web pages that meet your search criteria to a certain degree. The most relevant matches are at the top of the search results – or at least that is the intention. Searching for interesting job vacancies, holiday destinations, new real estate, electronic devices, dating partners on a e-dating site, books on Oracle technology are other examples of searches that look for best matches given criteria, not necessarily perfects fits for all criteria More >