Posts tagged sql
Using SQL UNPIVOT to prepare data for dynamic ADF Faces User Interfaces
0The UNPIVOT operator was introduced in Oracle Database 11g – see for example: http://technology.amis.nl/blog/2421/the-oracle-11g-unpivot-operator-turning-columns-into-rows. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.
We can use the UNPIVOT operator in ADF BC ViewObjects to derive multiple rows from individual records. Subsequently, these rows can be filtered by the property they represent. This sounds vague – I know. I will use this approach to achieve the following functionality: a page that displays a bar chart with values per department. Depending on the setting of a radio group, the chart will display the number of employees, the maximum salary, the average experience of the average salary per department. The chart is based on a single ViewObject that contains a single query More >
Using the ADF DVT Radar Graph for comparing series – further analyzing ODTUG Kaleidoscope 2011 Session Schedule
0I have always had a fascination for the Data Visualization capabilities of all the tools and technologies I have worked with. For example: I worked with Oracle Graphics 2.0, back in 1994, and liked it! Fast forwarding through the years – I am now wetting my appetite with ADF’s Data Visualization Tags (DVT) – an impressive array of graphs, gauges, charts and other ways of visualizing data.
One interesting type of graph that I have not actually used before, is the Radar Chart. It is an interesting type of visualization that can plot in one graph values for multiple series against multiple dimensions and make it easy to compare them (the series) with one another. Granted, you can do something similar with a multi-series line chart, yet the comparison is somewhat more pronounced in the radar graph.
See for example the next graph. It plots values for three series – each serie represents a region in the world. For each of the tracks at the ODTUG Kaleidoscope 2011 conference, the percentage of sessions in the track delivered by presenters from the region represented by a serie is plotted. Thus we can see that over 90% of the sessions in the BI and Oracle EPM track is delivered by More >
How to use an outerjoin to synchronise data
0There is often a need to synchronize data from a query. For example reading external data and update the tables. Sometimes it can be achieved by a single merge statement, but this is not always possible. Mind, for example, the need to update more than one table, or updating the data using an api. As an example we want to grant and revoke privileges on database objects. Tables needing select, insert, update and delete privileges, packages execute and views select privileges. The table and packages also get the grant option for the select and execute privilege. (more…)
The Knight's Challenge – Recursive SQL Queries make a move on the Chess board
3In my quest to position ‘connect by’ (and its 11g successor, Recursive Subquery) as mechanism for recursive queries rather than just an hierarchical query facility, I stumbled across a simple, fairly meaningless challenge to take on: a knight on a chess board – and the question of finding its way from one square to another. Recursive querying in general consists of two elements:
- the initial situation, the starting point (the n=1 step)
- the algoritm to get from one stage to the next (the n to n+1 step)
A third, mandatory, element is the termination of the query: when is the journey complete, what is the maximum value for n or what is the exit condition. (more…)
Database Transaction Recorder – Adding Who to When and What to make Flashback take over from Journalling tables
2In previous articles, I have suggested that the arrival of the Flashback Data Archive in Oracle RDBMS 11g allowed us to finally say goodbye to journalling tables. Keeping track of all changes and previous states of our records in our own dedicated tables is no longer required, was my statement (for example in http://technology.amis.nl/blog/2453/oracle-11g-total-recall-flashback-in-the-hands-of-database-designers-and-application-developers-at-last-and-the-end-of-journalling-tables). Not only would using Flashback Data Archives require less programming, it improves performance for DML operations substantially and also provides a lot of functionality at our fingertips, as Flashback Queries easily incorporate historical records in straightforward SQL queries and using dbms_flashback we can even turn back time and regard all data as it was back then, using the same application and all the same queries.
In the not too distant past, Flashback knew quite a long list of limitations that made it almost impossible to make any changes to a table definition and still retain the historical data. With recent improvements in 11gR2, most of these limitations have been removed and DDL such as More >
Anti-Search patterns – SQL to look for what is NOT there – Part Two
2It is a strange thing really: querying the database to find missing data. Queries usually report on what is there. Not the queries in this article (and its prequel): they report on what is not there. We explore a number of approaches to uncovering missing information – because the fact that something is not found in itself is a fact we may want to know about. And it gives me a nice motive to demonstrate a number of fun SQL features.
We are still very much engaged in the EMP and DEPT tables in the SCOTT schema that back in the early ’80s was pivotal in demonstrating the capabilities of the first Oracle RDBMS releases. It still does an excellent job for most of my demos today. Must be me…
This article delves into urgent matters such as ‘which letters do not occur in the names of the employees’, ‘in which months of the year was no one hired’, ‘who in each department does not have a colleague with less experience (or a lower salary)’, ‘which employees do not have a colleague in the same job (either in their own department or in the entire company)’, and more.
Recent Comments