22.3. Auxiliary Function Tables

SQL is not a computational language like FORTRAN and the specialized math packages. It typically does not have the numerical analysis routines to compensate for floating-point rounding errors, or algebraic reductions in the optimizer. But it is good at joins.

Most auxiliary lookup tables are for simple decoding, but they can be used for more complex functions. Let’s consider two financial calculations that you cannot do easily: the Net Present Value (NPV) and its related Internal Rate of Return (IRR). Let me stop and ask: how would you program the NPV and IRR in SQL? The answer posted on most newsgroup replies was to write a procedure directly from the equation in the vendor-specific 4GL language and then call ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.