//AMIS Technology Blog » subquery factoring

Posts tagged subquery factoring

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
3 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…)

image.png

Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring

0
3 months ago
by Lucas Jellema in ,

The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:

The challenges I will discuss in this article are simple:

  • which sequences of three color observations occur in the data set
  • how often do these sequences of three occur (give me the top 3 sequences)
  • given the colors that have been observed, which combinations of three can be created
  • which possible combinations (or color sequences of three colors) have not been observed at all

Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.

(more…)

image.png

Oracle SQL: Using subquery factoring in an INSERT statement

0
3 months ago
by Lucas Jellema in , ,

I use subquery factoring a lot, to make my SQL queries easier to write. This basically means that most of my queries these days start with WITH rather than SELECT. (this feature was introduced in the 9i release of the Oracle Database).

Today I was wondering whether I can also use subquery factoring in INSERT statements. You probably are familiar with INSERT statements that go like:

INSERT INTO <tablename>

(column1, columns2,….)

SELECT value1,value2,…

FROM … [rest of the query]

Such statements allow us to quickly and in a focused way load a table with records read from other tables (or PL/SQL Table Functions).

The question I had was: can I use WITH <subquery> as <alias>[, <subquery> as <alias>] SELECT value1,value2 from subquery,…. [rest of the query] inside an INSERT statement.

Best way to find it out – try and do it.

(more…)

Go to Top