sql - Oracle view - sum to variable -

how can shorten following select statement?

create or replace view v_amv_plg_quotes_50_ms  select      sum(nvl(ask_size,0))    ask_volume,   case when            sum(case when nvl(ask_size,0)=0 case when base_curr_ask_price=0 0 else 1 end else ask_size end)=0 1       else sum(case when nvl(ask_size,0)=0 case when base_curr_ask_price=0 0 else 1 end else ask_size end) end     ask_price,       exchange_sk         exchange_sk,      product_sk          product_sk,      business_date       business_date       s_amv_plg_quote_agg quotes  nvl(base_curr_ask_price ,0) > 0 group      exchange_sk,      product_sk,      business_date  

notice long calculation appears twice.. can insert variable somehow? , use case operator on variable? (notice have sum function there..)

you can't use create , use variable within view. if want avoid repeating nested case statement, move (and nvl) in-line view:

select      sum(my_ask_size)    ask_volume,      case when sum(my_ask_price)=0 1          else sum(my_ask_price)         end             ask_price,     exchange_sk         exchange_sk,      product_sk          product_sk,      business_date       business_date  (     select         exchange_sk, product_sk, business_date,         nvl(ask_size,0) my_ask_size,         case when nvl(ask_size,0)=0                 case when base_curr_ask_price=0 0 else 1 end             else ask_size             end my_ask_price      s_amv_plg_quote_agg quotes      nvl(base_curr_ask_price ,0) > 0 ) group      exchange_sk,      product_sk,      business_date; 

the nvl , case performed once, , aliased my_ask_size , my_ask_price respectively; , aliases used in outer query, including within sum.

if want avoid repeating sum too, move in-line view well, along group-by clause:

select      ask_volume          ask_volume,      case when ask_price=0 1         else ask_price         end             ask_price,     exchange_sk         exchange_sk,      product_sk          product_sk,      business_date       business_date  (     select         exchange_sk, product_sk, business_date,         sum(nvl(ask_size,0)) ask_volume,         sum(case when nvl(ask_size,0)=0                 case when base_curr_ask_price=0 0 else 1 end             else ask_size             end) ask_price      s_amv_plg_quote_agg quotes      nvl(base_curr_ask_price ,0) > 0     group          exchange_sk,          product_sk,          business_date ); 

most of column aliases redundant i've stuck pattern.

this largely cosmetic though; oracle's optimiser clever enough perform actual calculation once anyway, wouldn't expect see (or any) performance difference.