Oracle Magazine, March/April 2019
Database Developer and DBA ETL ORACLE MAGAZINE MARCH APRIL 2019 107 Listing 5 Business requirement translated to SQL query SQL select REGION max SALE_ AMOUNT 2 from EMP e 3 SALES_ TRANSACTIONS s 4 where e JOB SALESMAN 5 and s EMPNO e EMPNO 6 and s LOCATION CA 7 and s TAX_ LEVY 10 8 or s GOVT_ SUBSIDY 0 9 group by REGION But this is probably an incorrect translation due to the ordering of operations implied but not explicitly stated by the business requirement It is most likely and worth confirming with the business stakeholders that the requirement when phrased with more precision was First identify the set of transactions for sales consultants from regions in California Then with that set of data filter where the tax levy is more than 10 or the government subsidy is nonzero Then with that reduced set of data find the highest transaction amount per region The SQL in Listing 5 does not fulfill this requirement and moreover is likely to have performance issues because the final predicate of OR GOVT_ SUBSIDY 0 becomes a standalone predicate that is not associated with the other conditions or even the joins Because the query performs an aggregation on the REGION column
You must have JavaScript enabled to view digital editions.
