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

View the Covers and the Table of Contents pages from every issue of this publication, all gathered together for easy browsing. Just flip pages and zoom as you normally do to see each issue's Cover and Table of Contents, then follow links directly to interesting content.