//AMIS Technology Blog » listagg

Posts tagged listagg

image.png

Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR

1
2 months ago
by Lucas Jellema in ,

Using Oracle SQL to resolve meaningful and slightly less serious challenges is one of my favorite (semi-)professional pastimes. In the last two weeks, I have been presenting on various topics including Oracle SQL to audiences in six cities all across India as part of the OTN Yathra 2013. These presentations and the interaction with the attendees on the various capabilities of SQL have inspired me in several ways. One of the outcomes is this article – also inspired by the fairly long journey home and the many flights within India. In this article I will use several powerful options in Oracle SQL to resolve some simple to ask questions. The SQL functions I am using include:

  • Insert with Multiple Subqueries
  • Insert generating some random data
  • LISTAGG for aggregating strings
  • LEAD to produce the result for one row using information from the next
  • PIVOT to present the data in a matrix format

The statements are straightforward (relatively), the data model is simple. You will like it.

(more…)

Vacatures bij AMIS services

Producing simple Pie Chart straight out of the Oracle Database – leveraging dbms_epg, CANVAS, LISTAGG and some JavaScript

0
1 year ago
by Lucas Jellema in , , , ,

Once upon a time, I had something of a reputation for creating Charts in SQL. Bar Charts, Pie Charts, Gantt Charts and several other unlikely chart types, all created with SQL. And all presented on the one stage SQL has: SQL*Plus. See for example such riveting articles like http://technology.amis.nl/blog/1890/creating-a-gantt-chart-in-sql, http://technology.amis.nl/blog/1937/gauge-charts-in-sql-or-how-to-produce-the-worlds-ugliest-speedometers and http://technology.amis.nl/blog/398/pie-charts-in-sql-how-pathetic-can-you-get. Now I am back, with another look at charts produced straight out of the Oracle Database – this time using a different stage to present the chart on. With the Embedded PL/SQL Gateway – introduced in release 10g – the Oracle Database has a different stage to present information on or a different user interface to interact through: the web browser.

This article demonstrates how we can present charts – in the web browser using the CANVAS tag – based directly on a PL/SQL package in the database. No middle tier is required in between, no additional libraries are frameworks need to be configured. It’s only between the package and your browser.

Granted, you have seen More >

SIG Event

Anti-Search patterns – SQL to look for what is NOT there – Part One

5
2 years ago
by Lucas Jellema in , , ,

Usually in SQL queries, you will try to retrieve information from a database by reading specific records from the relevant tables. However, there are situations where what you want to learn from a query is the fact that something is NOT there. So instead of querying for existing records, you are looking for records that do not exist. This article discusses a number of such situations and demonstrates several “anti-queries”. Where possible, I have used the EMP and DEPT table of the classical SCOTT sample schema to illustrate the point.

Some of these examples are perhaps better used to illustrate specific SQL features – such as CONNECT_BY_ISLEAF, PARTITION OUTER JOIN, LISTAGG, MINUS, SCALAR SUBQUERY and more – than to answer the questions at hand.

(more…)

Go to Top