Transposing a Result Set Using Oracle’s MODEL Clause
select max(d10) d10,
max(d20) d20,
max(d30) d30
from (
select d10,d20,d30
from ( select deptno, count(*) cnt from emp group by deptno )
model
dimension by(deptno d)
measures(deptno, cnt d10, cnt d20, cnt d30)
rules(
d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,
d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end
)
)
Extracting Elements of a String from Unfixed Locations
We have a string field that contains serialized log data. We want to parse through the string and extract the relevant information. Unfortunately, the relevant information is not at fixed points in the string.
Sample Input
We want to extract the values between the square brackets
Sample Output
create view V
as
select 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg from dual
union all
select 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg from dual
union all
select 'call:[F_GET_ROWS()]b1:[ROSEWOOD…SIR]b2:[44400002]77.90xxxxx' msg from dual
union all
select 'film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx' msg from dual
select substr(msg,
instr(msg,'[',1,1)+1,
instr(msg,']',1,1)-instr(msg,'[',1,1)-1) first_val,
substr(msg,
instr(msg,'[',1,2)+1,
instr(msg,']',1,2)-instr(msg,'[',1,2)-1) second_val,
substr(msg,
instr(msg,'[',-1,1)+1,
instr(msg,']',-1,1)-instr(msg,'[',-1,1)-1) last_val
from V
Finding the Number of Days in a Year (an Alternate Solution)
We want to find the number of days in a year.
select 'Days in 2021: '||
to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD')
as report
from dual
union all
select 'Days in 2020: '||
to_char(add_months(trunc(
to_date('01-SEP-2020'),'y'),12)-1,'DDD')
from dual
Searching for Mixed Alphanumeric Strings
We have a column with mixed alphanumeric data and want to return those rows that have both alphabetical and numeric characters.
with v as (
select 'ClassSummary' strings from dual union
select '3453430278' from dual union
select 'findRow 55' from dual union
select '1010 switch' from dual union
select '333' from dual union
select 'threes' from dual
)
select strings
from (
select strings,
translate(
strings,
'abcdefghijklmnopqrstuvwxyz0123456789',
rpad('#',26,'#')||rpad('*',10,'*')
) translated
from v
) x
whereinstr(translated,'#') > 0
and instr(translated,'*') > 0
Converting Whole Numbers to Binary
We want to convert a whole number to its binary representation.
Because of MODEL’s ability to iterate and provide array access to row values, it is a best choice for this operation.
select ename,
sal,
(
select bin
from dual
model
dimension by ( 0 attr )
measures ( sal num, cast(null as varchar2(30)) bin, '0123456789ABCDEF' hex
)
rules iterate (10000) until (num[0] <= 0) (
bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
num[0] = trunc(num[cv()]/2)
)
) sal_binary
from emp
Pivoting a Ranked Result Set
We want to rank the values in a table and then pivot the result set into three columns. The idea is to show the top three, the next three, and then all the rest.
SELECT
MAX(
CASE grp
WHEN 1 THEN
rpad(ename, 6)
|| ' ('
|| sal
|| ')'
END
) top_3,
MAX(
CASE grp
WHEN 2 THEN
rpad(ename, 6)
|| ' ('
|| sal
|| ')'
END
) next_3,
MAX(
CASE grp
WHEN 3 THEN
rpad(ename, 6)
|| ' ('
|| sal
|| ')'
END
) rest
FROM
(
SELECT
ename,
sal,
rnk,
CASE
WHEN rnk <= 3 THEN
1
WHEN rnk <= 6 THEN
2
ELSE
3
END grp,
ROW_NUMBER()
OVER(PARTITION BY
CASE
WHEN rnk <= 3 THEN
1
WHEN rnk <= 6 THEN
2
ELSE
3
END
ORDER BY
sal DESC, ename
) grp_rnk
FROM
(
SELECT
ename,
sal,
DENSE_RANK()
OVER(
ORDER BY
sal DESC
) rnk
FROM
emp
) x
) y
GROUP BY
grp_rnk
Explanation
Adding a Column Header into a Double Pivoted Result Set
We want to stack two result sets and then pivot them into two columns. Additionally, we want to add a “header” for each group of rows in each column. For example, we have two tables containing information about employees working in different areas of development in your company
create table IT_research (deptno number, ename varchar2(20))
insert into IT_research values (100,'HOPKINS')
insert into IT_research values (100,'JONES')
insert into IT_research values (100,'TONEY')
insert into IT_research values (200,'MORALES')
insert into IT_research values (200,'P.WHITAKER')
insert into IT_research values (200,'MARCIANO')
insert into IT_research values (200,'ROBINSON')
insert into IT_research values (300,'LACY')
insert into IT_research values (300,'WRIGHT')
insert into IT_research values (300,'J.TAYLOR')
create table IT_apps (deptno number, ename varchar2(20))
insert into IT_apps values (400,'CORRALES')
insert into IT_apps values (400,'MAYWEATHER')
insert into IT_apps values (400,'CASTILLO')
insert into IT_apps values (400,'MARQUEZ')
insert into IT_apps values (400,'MOSLEY')
insert into IT_apps values (500,'GATTI')
insert into IT_apps values (500,'CALZAGHE')
insert into IT_apps values (600,'LAMOTTA')
insert into IT_apps values (600,'HAGLER')
insert into IT_apps values (600,'HEARNS')
insert into IT_apps values (600,'FRAZIER')
insert into IT_apps values (700,'GUINN')
insert into IT_apps values (700,'JUDAH')
insert into IT_apps values (700,'MARGARITO')
select max(decode(flag2,0,it_dept)) research,
max(decode(flag2,1,it_dept)) apps
from (
select sum(flag1)over(partition by flag2 order by flag1,rownum) flag,
it_dept,
flag2
from (
select 1 flag1, 0 flag2,
decode(rn,1,to_char(deptno),' '||ename) it_dept
from (
select x.*, y.id, row_number()over(partition by x.deptno order by y.id) rn
from (
select deptno,
ename,
count(*)over(partition by deptno) cnt
from it_research
) x,
(select level id from dual connect by level <= 2) y
)
where rn <= cnt+1
union all
select 1 flag1, 1 flag2,
decode(rn,1,to_char(deptno),' '||ename) it_dept
from (
select x.*, y.id,
row_number()over(partition by x.deptno order by y.id) rn
from (
select deptno,
ename,
count(*)over(partition by deptno) cnt
from it_apps
) x,
(select level id from dual connect by level <= 2) y
)
where rn <= cnt+1
) tmp1
) tmp2
group by flag
Parsing Serialized Data into Rows
We have serialized data (stored in strings) that you want to parse and return as rows. The values are delimited by colons, and a string may or may not have all three entries
create view V
as
select 'entry:stewiegriffin:lois:brian:' strings
from dual
union all
select 'entry:moe::sizlack:'
from dual
union all
select 'entry:petergriffin:meg:chris:'
from dual
union all
select 'entry:willie:'
from dual
union all
select 'entry:quagmire:mayorwest:cleveland:'
from dual
union all
select 'entry:::flanders:'
from dual
union all
select 'entry:robo:tchi:ken:'
from dual;
with cartesian as (
select level id
from dual
connect by level <= 100
)
select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,
max(decode(id,2,substr(strings,p1+1,p2-1))) val2,
max(decode(id,3,substr(strings,p1+1,p2-1))) val3
from (
select v.strings,
c.id,
instr(v.strings,':',1,c.id) p1,
instr(v.strings,':',1,c.id+1)-instr(v.strings,':',1,c.id) p2
from v, cartesian c
where c.id <= (length(v.strings)-length(replace(v.strings,':')))-1
)
group by strings
order by 1
Explanation
Calculating Percent Relative to Total
We want to report a set of numeric values, and you want to show each value as a percentage of the whole.
Sample Output
select job,num_emps,sum(round(pct)) pct_of_all_salaries
from (
select job,
count(*)over(partition by job) num_emps,
ratio_to_report(sal)over()*100 pct
from emp
)
group by job,num_emps
