Oracle Magazine, September/October 2018
Database Developer and DBA ETL ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 92 4 group by deptno 5 6 select from last_ hire DEPTNO MAX HIRED 30 03 DEC 81 20 12 JAN 83 10 23 JAN 82 At first glance the WITH clause may seem like merely a waste of keystrokes but its true benefit can be seen when the functional requirements of a query become more complex Consider this business requirement given to a developer Find the total salary paid by each department get the average of these totals and list those departments above that average which needs to be expressed with a SQL statement Rather than tackling the entire task holistically developers using the WITH clause can tackle the task in smaller more manageable pieces much as they would in their 3GL application code I can start with the first part of the requirement Find the total salary paid by each department and construct an appropriate query for just that component as shown in Listing 7
You must have JavaScript enabled to view digital editions.