Factorial in Oracle SQL – using both new Recursive Subquery and classic Connect By approach
I regularly teach a masterclass on Oracle SQL. One of the topics I explore in depth is the use of the CONNECT BY query style to perform not just hierarchical queries but also networking or even generic recursive SQL. In Oracle Database 11g, the recursive subquery was formally introduced, the SQL Standard’s approach to this style of querying. The Recursive Subquery even stronger suggest recursive operations to be performed of course, but classic connect by can do that job as well.
One archetypical example of a recursive operation is the calculation of a factorial: n! = 1* 2 * 3 *â€¦. * (n-1) * n.
In this short post I will show both the new, straightforward 11g based solution as well as the classic approach with CONNECT BY – that may not looks as recursive, but still very much is.
The 11g Recursive Subquery Factoring will have you quickly create the query to calculate factorials:
The first subquery is used to inject the operand into the query. Here we are going to calculate the factorial of 5. The recursive subquery is called factorial. It will return f (operand) and outcome (factorial for operand). The first iteration is for f == 0 with outcome 1. The factorial for the next operand value is calculated taking the previous outcome and multiplying it with the operand. This recursive calculation is done in the second part of the factorial subquery – the one that refers to ‘itself’. The iterations are performed for as long as the operand value is smaller than the required value in params.
The result of this query is:
with params as ( select 5 p from dual ) , Factorial (f, outcome) as ( select 0 f , 1 outcome from dual union all select f+1 , outcome * (f+1) outcome from Factorial join params on (f < p) ) select * from Factorial join params on (f = p)
Now the classic CONNECT BY approach. It is really not very different at heart, even though it looks quite different:
The params subquery is the same as before. The factors subquery traverses the values 1..params.p, in this case 1 through 5. For each value, the path string is composed as (*1*2*…*(n-1)*n).
The final trick in the last query is to use the xmlquery operation to evaluate the string (minus the first *) and calculate the factorial outcome.
with params as ( select 5 p from dual ) , factors as ( select level f , sys_connect_by_path( level ,'*') path from params connect by level <= p ) select f , xmlcast( xmlquery( substr(path, 2) returning content ) as number(4)) result from factors
- Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring
- Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
- Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
- Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
- The Knight's Challenge – Recursive SQL Queries make a move on the Chess board
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation
- AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december