Is there a way to avoid using subqueries in Oracle calculating partial values of complicated expressions with code being still easily maintainable?

Question

I'm trying to select a value based on a complicated expression from a data table, having a select like this where for future maintainability I'm not writing the whole expression in one, instead I'm calculating partial results in subselects :

(The query is not really important, the concept is, so please bear with me as I have depersonalized this sample query. I'm using one or two letter aliases just for presentation purposes)

select t.*,
       (1 - nvl(min(t.O / t.MA) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4), 0)) *
       t.MA as V --value regulated by overhead
  from (select t.*,
               decode(sign(t.MA - t.M), 1, t.MA - t.M, to_number(null)) as O --overhead
          from (select t.*,
                       (1 - (max(t.PD) over(partition by t.fk_1, t.fk_2, t.fk_3, t.fk_4))) * t.ASZ as MA --value regulated by maximum percent difference
                  from (select t.*,
                               t.D / t.ASZ as PD --precent of diff      
                          from (select t.*,
                                       t.ASZ - t.BE as D --difference
                                  from (select l.fk_1, 
                                               l.fk_2, 
                                               l.fk_3, 
                                               l.fk_4,
                                               l.fk_5,
                                               l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
                                               l.A, --Ratio
                                               l.SB, --Sum of values
                                               l.M, --Maximum of value
                                               decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio      
                                          from vw_data l,
                                       ) t) t) t) t) t;

I was wondering if this could be rewritten in a way similar to this (which syntax doesn't work atm):

select l.fk_1,
       l.fk_2,
       l.fk_3,
       l.fk_4,
       l.fk_5,
       l.be, --Value that needs regulation by ratio on of Sum of values and maximum
       l.a, --Ratio
       l.sb, --Sum of values
       l.m, --Maximum of value
       decode((l.sb * l.a), 0, to_number(null), (l.sb * l.a)) as asz, --Sum distributed as of given ratio      
       asz - be as d, --difference
       d / asz as pd, --precent of diff  
       (1 - (max(pd) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4))) * asz as ma, --value regulated by maximum percent difference
       decode(sign(ma - l.m), 1, ma - l.m, to_number(null)) as o, --overhead                  
       (1 - nvl(min(o / ma) over(partition by l.fk_1, l.fk_2, l.fk_3, l.fk_4), 0)) * ma as v --value regulated by overhead
  from vw_data l

If you prefer to have it formatted a little bit better but much longer:

SELECT t.*,
       ( 1 - nvl( MIN( t.O / t.MA ) OVER ( PARTITION BY t.fk_1,
                                                        t.fk_2,
                                                        t.fk_3,
                                                        t.fk_4 ),
                                           0 ) ) *
           t.MA AS V --value regulated by overhead
FROM ( SELECT t.*,
              DECODE( sign( t.MA - t.M ),
                      1,
                      t.MA - t.M,
                      TO_NUMBER( null ) ) AS O --overhead
          FROM ( SELECT t.*,
                       ( 1 - ( MAX( t.PD ) OVER ( PARTITION BY t.fk_1,
                                                               t.fk_2,
                                                               t.fk_3,
                                                               t.fk_4 ) ) ) *
                           t.ASZ AS MA --value regulated by maximum percent difference
                 FROM ( SELECT t.*,
                               t.D / t.ASZ AS PD --precent of diff      
                        FROM ( SELECT t.*,
                                      t.ASZ - t.BE AS D --difference
                                  FROM ( SELECT l.fk_1, 
                                                l.fk_2, 
                                                l.fk_3, 
                                                l.fk_4,
                                                l.fk_5,
                                                l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
                                                l.A, --Ratio
                                                l.SB, --Sum of values
                                                l.M, --Maximum of value
                                                DECODE( ( l.SB * l.A ),
                                                        0,
                                                        TO_NUMBER( NULL ),
                                                        ( l.SB * l.A ) ) AS ASZ --Sum distributed as of given ratio      
                                         FROM vw_data l,
                                       ) t ) t ) t ) t ) t;

I was wondering if this could be rewritten in a way similar to this (which syntax doesn't work atm):

SELECT l.fk_1,
       l.fk_2,
       l.fk_3,
       l.fk_4,
       l.fk_5,
       l.be, --Value that needs regulation by ratio on of Sum of values and maximum
       l.a, --Ratio
       l.sb, --Sum of values
       l.m, --Maximum of value
       DECODE( ( l.sb * l.a ),
               0,
               TO_NUMBER( NULL ),
               ( l.sb * l.a ) ) AS asz, --Sum distributed as of given ratio      
       asz - be AS d, --difference
       d / asz AS pd, --precent of diff  
       ( 1 - ( MAX( pd ) OVER ( PARTITION BY l.fk_1,
                                             l.fk_2,
                                             l.fk_3,
                                             l.fk_4 ) ) ) * asz AS ma, --value regulated by maximum percent difference
       DECODE( SIGN ( ma - l.m ),
               1,
               ma - l.m,
               TO_NUMBER( NULL ) ) AS o, --overhead                  
       ( 1 - nvl( MIN( o / ma ) OVER ( PARTITION BY l.fk_1,
                                                    l.fk_2,
                                                    l.fk_3,
                                                    l.fk_4 ), 0 ) ) * ma AS v --value regulated by overhead
  FROM vw_data l

score 1 · Accepted Answer · edited May 23 '17 at 10:30

Not really. You can't refer to a column alias in the same level of query, except in the order by clause - which makes your ideal query impossible sadly. For instance, when you try to do asz - be AS d the asz will not be recognised as there is no column with that name in the base table, and the alias with that name doesn't exist yet.

From the documentation:

c_alias
Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

There are various explanations around about why, like this one, though they tend to be about why you can't use the alias in a where, group-by or having clause. It's maybe less obvious why you can't use them again within the same select list. But consider that you can alias an expression to be the same as a real column name, so you could attempt do something like:

select abs(be) as be, be - asz as d, ...

... which introduces the possibility of additional ambiguity - is the be in your calculation referring to the original column value, or the modified value in the alias? I'm not suggesting you are or would do that, of course. Slightly more likely is that someone could alter the table to add a column called asz, which would confuse existing queries. But whatever the reasoning, you can't refer to a column alias in the same select list, either.

You could use subquery factoring instead of nested subqueries - which would at least let you list the intermediate steps in order, which might a bit more intuitive; but it doesn't really help that much:

with t1 as (
  select l.fk_1, 
         l.fk_2, 
         l.fk_3, 
         l.fk_4,
         l.fk_5,
         l.BE, --Value that needs regulation by ratio on of Sum of values and maximum
         l.A, --Ratio
         l.SB, --Sum of values
         l.M, --Maximum of value
         decode((l.SB * l.A), 0, to_number(null), (l.SB * l.A)) as ASZ --Sum distributed as of given ratio      
  from vw_data l),
t2 as (
  select t1.*,
         t1.ASZ - t1.BE as D --difference
  from t1),
t3 as (
  select t2.*,
         t2.D / t2.ASZ as PD --precent of diff
  from t2),
t4 as (
  select t3.*,
         (1 - (max(t3.PD) over (partition by t3.fk_1, t3.fk_2, t3.fk_3, t3.fk_4)))
           * t3.ASZ as MA --value regulated by maximum percent difference
  from t3),
t5 as (
  select t4.*,
         decode(sign(t4.MA - t4.M), 1, t4.MA - t4.M, to_number(null)) as O --overhead
  from t4)
select t5.*, 
       (1 - nvl(min(t5.O / t5.MA) over(partition by t5.fk_1, t5.fk_2, t5.fk_3, t5.fk_4), 0))
         * t5.MA as V --value regulated by overhead
from t5;

It's somewhat a matter of preference in this scenario.

Otherwise you'd need to stick to a single level of query and repeat all the calculations multiple times, which is what you're trying to avoid. Or create a load of functions that do the various calculations (with repetition between them), which would be extra overhead and even more opaque.

Is there a way to avoid using subqueries in Oracle calculating partial values of complicated expressions with code being still easily maintainable?

1 Answers1