Wednesday, 2 October 2013

Formatting 101 - Nested single row functions

Sometimes to get the data displayed exactly how you want it, you need to employ some nested functions.

with t as (
  select null n from dual union all 
  select 0  from dual union all 
  select 0.5 from dual union all 
  select 4 from dual union all 
  select 4.5 from dual)
select to_number(n) verbatim
  ,TO_CHAR(n,'fm90.9') no_trail
  ,TO_CHAR(n,'fm90.0') trail_zero
  ,RTRIM(TO_CHAR(n,'fm90.9'),'.') trail_decimal
  ,RTRIM(TO_CHAR(NVL(n,0),'fm90.9'),'.') and_null
from t
/

  VERBATIM NO_TRAIL TRAIL_ZERO TRAIL_DECIMAL AND_NULL
---------- -------- ---------- ------------- --------
                                             0        
         0 0.       0.0        0             0        
       0.5 0.5      0.5        0.5           0.5      
         4 4.       4.0        4             4        
       4.5 4.5      4.5        4.5           4.5    

I'm curious - does anyone have formatting techniques the prefer/hate to help follow the brackets & commas?

For example, I might indent similar to JavaScript, and line up brackets/commas.
<< my_loop >>
FOR r_rec IN (
  SELECT 
    DECODE(this_value
          ,'Y', 'Yes'
          ,'N', 'No'
          ,'Maybe')
  FROM   that_table
) LOOP
  null;
END LOOP my_loop;

Scott
Post a Comment