If you are wondering what SPOD is, then check out this blog post by Steven Feuerstein. Suppose you want to create a SQL statement (for a view or cursor or something else) where you want the value of one mathematical function to be used as input for another mathematical function. For simplicity reasons I am just using some simple numbers, but you can make this as complex as you want.

Of course you can easily add basically the same function over and over again in the statement, like this:

SELECT ( 2 * 3 ) a
     , ( ( 2 * 3 ) * 4 ) b
     , ( ( 2 * 3 ) * 5 ) c
     , ( ( ( 2 * 3 ) * 5 ) + 10 ) d
  FROM DUAL;

But, what if the formula for c changes. That would mean changing both the formula for c and for d. Or even worse, what if the formula for a changes. That would mean changing all the formulas for a, b, c and d.

Not really SPOD (if you don’t know what it is by now, it Single Point Of Definition). We really want to enter each formula just once and then be able to use it results in another formula. This is where the inline views come into play. Remember that inline views are noting more than SQL statements in the from clause. It’s like creating a view at runtime, without it being physically created in the database.

If you want to select something from a table, this table has to be in the from clause. It’s the same with inline views. If you want to select anything from a ‘lower level’, be sure to include it in the select of the inline view. I call it, bubbling up values (like the bubbles coming to the surface in your favorite soft-drink).

Consider the following code:

SELECT a
     , b
     , c
     , c + 10 d
  FROM ( SELECT a * 4 b
              , a * 5 c
              , a
          FROM ( SELECT ( 2 * 3 ) a
                  FROM DUAL ))

Looks more complex, but at least we’ve achieved Single Point Of Definition. Remember the following things:

• The formula you will be using as input for other formulas must be in the deepest level.
• Everything you want to be able to select on a higher level, must be selected on the level directly below that one.
• Use aliases to be able to reference the result in a higher level.