Posts tagged collect
Emulate Cross Service Joins in SOA Suite with Table Functions and Database Adapter
0It was fairly difficult to come up with a title for this article that sort of covers the content. To me it is quite clear what this is about – but how to convey that in a title? Let me explain: today in our project we discussed the implementation of a data service. The service operation under scrutiny takes a city as input and returns a list all open orders from customers located in that city. Nothing very special there. The interesting complication lies in the fact that the customers are part of a different domain than the orders. This means – under our architecture guidelines – that we cannot create a single SQL query that joins together the customers table with the orders table. A database link to join the tables across databases is out of the question and even if these tables currently reside in the same database – such a join is not allowed. Different data domains are treated as independent entities and no direct dependencies between the two should be created. Every design has pass the check ‘will it still work if one of the domains involved were to be relocated to the cloud or be replaced by a third party application’.
The architecture is service oriented. Every domain More >
Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article introduced the LISTAGG operator, new in 11gR2.
An example usage of LISTAGG:
select deptno , avg(sal) avg_sal , listagg( ename, ',') within group (order by sal) enames from emp group by deptno
Analyzing Match Scores in 'fuzzy searches' – explaining the shades of gray in a non-binary world
In a previous article – Not all has to be black and white in SQL Queries – , I discuss how not all searches are about perfect matches. The search for your lifelong partner, or your next job are fine examples of searches where a perfect match is hard to obtain. No single person or no single job will score one 100% on all your criteria. Unfortunately, such binary searches is exactly what SQL is good at. WHERE ALL CRITERIA ARE MET is the filter SQL applies to the records inspected. Of course you can use OR operators to leave some room for non-perfect matches and instead of using the equality (=) you can go for between or LIKE. But a structured approach to ‘fuzzy’ searches where the shades of gray most searches inevitably end up in is not readily available.
The prequel to this articles takes a few first steps in illustrating how scores against different criteria can be collected, how different weight factors for search criteria can be taken into account and how the first two search-match-patterns (number range, discrete values) started to emerge. One major question we ended that article with: how can we explain when the search is performed and the match scores for each record is More >
Recent Comments