Let’s learn 3 functions from http://docs.oracle.com
1. nvl(expr1,expr2)
nvl lets you replace null (returned as a blank) with a string in the results of a query. if expr1 is null, then nvl returns expr2. if expr1 is not null, then nvl returns expr1.
2. stats_mode
STATS_MODE takes as its argument a set of values and returns the value that occurs with the greatest frequency. If more than one mode exists, Oracle Database chooses one and returns only that one value.
3. TO_CHAR (datetime)
Convert the current date and time to string.
Example at work:
select
ID
,case
when nvl(manager_id,-1) not in (-1,3000,150,13,1,2,14) then ‘new’
when is_group1=’y’ then ‘group1’
else ‘old’
end as groups
,sum(case when charge + charge_discount< 0 then 0 else charge+ charge_discount end) as charge
,min(day) first_month
,stats_mode(case when threshold=’n’ then 0 when threshold is null then 0 else 1 end) as flag
,sum(case when to_char(day,’mm’)=to_char(add_months(to_date(‘{run_date_yyyymmdd}’, ‘yyyymmdd’),-1),’mm’) then units_number else 0 end) time_units
from u_table
where
location=1234567
and day between trunc(add_months(to_date(‘{run_date_yyyymmdd}’, ‘yyyymmdd’),-12),’month’) and trunc(to_date(‘{run_date_yyyymmdd}’, ‘yyyymmdd’),’month’)-1
and units>0
group by 1，2