Everyday SQL (5)

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