Взято с RSDN.ru:
Получил только что письмо от нашего оракловского админа ! 10 баллов !!! 8-)) Отдел уже полчаса валяется под столами
P.S. Вопрос на догадливость — зачем оракловский админ СРОЧНО разыскивает автора этого запроса?
Уважаемые Дамы и Господа Программисты и Технологи!
СРОЧНО разыскиваю автора нижеприведённого запроса, выполняемого на сервере [вырезано цензурой] пользователем [вырезано цензурой]!
CODE
select max(num) num,
max(weigth) weigth,
max(nod) nod,
max(station) station,
max(namegruz) namegruz,
max(nodname) nodname,
max(sort) sort,
max(five) five,
max(six) six,
max(seven) seven,
max(eight) eight,
max(nine) nine,
max(ten) ten,
max(eleven) eleven,
max(twelve) twelve,
max(thirteen) thirteen
from (-- Это общий запрос по Отделениям
select count(*) num,
sum(pogr_vag.vesgruz) weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
-- Этот запрос дает разбивку по видам грузов
select count(*) num,
sum(pogr_vag.vesgruz) weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
-- Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
count(*) five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 20 and 29
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
count(*) five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 20 and 29
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
count(*) six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 40 and 49
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
count(*) six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 40 and 49
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
count(*) seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 60 and 69
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
count(*) seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 60 and 69
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
count(*) eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 70 and 79
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
count(*) eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 70 and 79
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
count(*) nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 80 and 89
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
count(*) nine,
0 ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 80 and 89
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
count(*) ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 90 and 99
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
count(*) ten,
0 eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps between 90 and 99
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
count(*) eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 93
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
count(*) eleven,
0 twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 93
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
count(*) twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 95
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
count(*) twelve,
0 thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 95
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr)
union
--Это общий запрос по Отделениям
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
'' station,
'' namegruz,
max(nsi_nod.name) nodname,
1 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
count(*) thirteen
from pogr_vag, nsi_esr, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 96
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod)
union
--Этот запрос дает разбивку по видам грузов
select 0 num,
0 weigth,
decode(grouping(nsi_nod.nod), 0, nsi_nod.nod, '') nod,
decode(grouping(nsi_nod.nod), 0, max(nsi_nod.name), '') station,
decode(grouping(namegr), 0, namegr, '') namegruz,
max(nsi_nod.name) nodname,
2 sort,
0 five,
0 six,
0 seven,
0 eight,
0 nine,
0 ten,
0 eleven,
0 twelve,
count(*) thirteen
from pogr_vag, nsi_esr, nsi_gr_op, nsi_nod
where pogr_vag.esrpogr = substr(nsi_esr.esr(+), 1, 5)
and pogr_vag.kodgruz_op = nsi_gr_op.kodgruz_op
and nsi_esr.nod = nsi_nod.nod
and pogr_vag.kodrps = 96
and (pogr_vag.data_pogr between :b2 and :b1)
group by rollup(nsi_nod.nod, namegr))
group by sort, nod, station, namegruz
order by sort, nod, station, namegruz