oracle views (very difficult)

[raf]

hi,
I've table: "pulizie_pm"


COD_IMM.........PIANO........COD_RITMI.....TIPO_SP AZIO.......AREA_LOCALI
02015149.........P01.............................. .............V..................11,20
02015149.........P01............01................ ...............................4,08
02015149.........P01.............................. .............O..................3,78
02015149.........P01............06................ ...............................296,73
02015149.........PTE.............................. .............V..................18,30
02015149.........PTE............01................ ................................5,12
02015149.........PTE............04................ ...............................14,15
02015149.........PTE............05................ ...............................32,32
02015149.........PTE.............................. .............O..................14,99
02015149.........PTE.............................. .............O..................15,21
02015149.........PTE............05................ ................................60,26
02015149.........PTE............06................ .................................53,96
02015149.........S01.............................. ..............V..................8,74
02015149.........S01.............................. ..............O..................29,55
02015149.........S01.............................. ...............O..................68,23
02015149.........S01............05................ .................................72,19
02015149.........S01............06................ .................................33,70

view N.1

CREATE OR REPLACE VIEW TEST1 ( COD_IMM,
PIANO, COD_RITMI, CONTA_RITMI, TOTAL_AREA
) AS SELECT cod_imm, piano, cod_ritmi, COUNT(COD_RITMI),SUM(area_locali) total_area
FROM pulizie_pm
WHERE cod_ritmi IS NOT NULL
GROUP BY cod_imm, piano, cod_ritmi



COD_IMM.........PIANO......COD_RITMI......CONTA_RI TMI......TOTAL_AREA
02015149.........P01.........01................... .............1..............4,08
02015149.........P01.........06................... .............1..............296,73
02015149.........PTE.........01................... .............1..............5,12
02015149.........PTE.........04................... .............1..............14,15
02015149.........PTE.........05................... .............2..............92,58
02015149.........PTE.........06................... .............1..............53,96
02015149.........S01.........05................... .............1..............72,19
02015149.........S01.........06................... .............1..............33,7

VIEW N.2

CREATE OR REPLACE VIEW MAX_TEST1 ( COD_IMM,
PIANO, COD_RITMI, TOTAL_AREA, MAX_AREA
) AS SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM test1 a)
WHERE max_area = total_area


COD_IMM..........PIANO......COD_RITMI......TOTAL_A REA......MAX_AREA
02015149..........P01..........06................. ...........296,73............296,73
02015149..........PTE..........05................. ............92,58............92,58
02015149..........S01..........05................. ............72,19............72,19

VIEW N.3

CREATE OR REPLACE VIEW MAX_TEST1CONTA ( COD_IMM,
COD_RITMI, MAX_RITMI, TOTAL_AREA ) AS SELECT COD_IMM,COD_RITMI, sum(conta_ritmi),SUM(TOTAL_AREA)
from test1
group by COD_IMM,COD_RITMI



COD_IMM.........COD_RITMI.......MAX_RITMI.......TO TAL_AREA
02015149.........01..............................2 .................9,2
02015149.........04............................... 1.................14,15
02015149.........05............................... 3.................164,77
02015149.........06............................... 3.................384,39

VIEW N.4

CREATE OR REPLACE VIEW COD_WITH_MAX_COD_RITMI ( COD_IMM,
PIANO, COD_RITMI, TIPO_SPAZIO, AREA_LOCALI
) AS SELECT bl.cod_imm,
bl.piano,
(CASE WHEN tipo_spazio = 'O'
THEN max.cod_ritmi
ELSE bl.cod_ritmi
END) cod_ritmi,
bl.tipo_spazio,
bl.area_locali
FROM pulizie_pm bl, max_test1 max
WHERE bl.cod_imm(+) = max.cod_imm
AND bl.piano(+) = max.piano

COD_IMM..........PIANO.......COD_RITMI.......TIPO_ SPAZIO..........AREA_LOCALI
02015149..........PTE............................. ...................V....................18,3
02015149..........PTE..........01................. ........................................5,12
02015149..........PTE..........06................. ........................................53,96
02015149..........PTE..........05................. ..................O....................15,21
02015149..........PTE..........05................. ........................................60,26
02015149..........PTE..........04................. ........................................14,15
02015149..........PTE..........05................. ...................O....................14,99
02015149..........PTE..........05................. ..........................................32,32
02015149..........P01............................. ......................V....................11,2
02015149..........P01..........01................. ...........................................4,08
02015149..........P01..........06................. ....................O....................3,78
02015149..........P01..........06................. ...........................................296,73
02015149..........S01............................. .....................V....................8,74
02015149..........S01..........05................. ....................O....................29,55
02015149..........S01..........06................. ..........................................33,7
02015149..........S01..........05................. .........................................72,19
02015149..........S01..........05................. ...................O....................68,23

VIEW N.5

CREATE OR REPLACE VIEW TEST4 ( COD_IMM,
COD_RITMI, TIPO_SPAZIO, TOTAL_AREA ) AS (SELECT cod_imm,
cod_ritmi,
null tipo_spazio,
SUM(area_locali) total_area
FROM cod_with_max_cod_ritmi
WHERE tipo_spazio = 'O'
OR tipo_spazio IS NULL
GROUP BY cod_imm, cod_ritmi)
UNION ALL
(SELECT cod_imm,
Null cod_ritmi,
'V' tipo_spazio,
SUM(area_locali) total_area
FROM cod_with_max_cod_ritmi
WHERE tipo_spazio = 'V'
GROUP BY cod_imm)

COD_IMM.........COD_RITMI.........TIPO_SPAZIO..... .......TOTAL_AREA
02015149.........01............................... ...................................9,2
02015149.........04............................... ..................................14,15
02015149.........05............................... ..................................292,75
02015149.........06............................... ...................................388,17
02015149.......................................... ..V..................................38,24


Now I must create a view TO INSERT "TIPO_SPZIO=V" in COD_RITMI with greater
frequency (max(max_ritmi)), but if the max it coincides it is necessary to take the area greater.

In this case from view MAX_TEST1CONTA I get:
cod_ritmi=05 max=3
cod_ritmi=06 max=3

I tried this view:
CREATE OR REPLACE VIEW TEST_FINALCONTA ( COD_IMM,
COD_RITMI, SOMMA ) AS select cod_imm,
cod_ritmi,
SUM(total_area) somma
from (select cod_imm,
(CASE WHEN cod_ritmi IS NOT NULL
THEN cod_ritmi
ELSE (SELECT cod_ritmi
FROM MAX_TEST1CONTA
WHERE max_ritmi = (SELECT MAX(max_ritmi)
FROM MAX_TEST1CONTA) )
END) cod_ritmi,
tipo_spazio,
total_area
from test4)
group by cod_imm, cod_ritmi


I get error: ORA-01427 because max(max_ritmi) has 2 records.
If max(max_ritmi) has more one records I must take the area greater, in this case cod_ritmi=06
and i'll have this result final:

COD_IMM.........COD_RITMI.........TIPO_SPAZIO..... .......TOTAL_AREA
02015149.........01............................... ................................9,2
02015149.........04............................... ..............................14,15
02015149.........05............................... ...............................292,75
02015149.........06............................... ...........................388,17+38,24=426,41

Thanks in advance!!!!

[balajiyes]

Hope this meets the requirement:

CREATE OR REPLACE VIEW TEST_FINALCONTA ( COD_IMM,
COD_RITMI, SOMMA ) AS select cod_imm,
cod_ritmi,
SUM(total_area) somma
from (select cod_imm,
(CASE WHEN cod_ritmi IS NOT NULL
THEN cod_ritmi
ELSE (SELECT MAX(cod_ritmi)
FROM MAX_TEST1CONTA
WHERE max_ritmi in (SELECT MAX(max_ritmi)
FROM MAX_TEST1CONTA) )
END) cod_ritmi,
tipo_spazio,
total_area
from test4)
group by cod_imm, cod_ritmi

[raf]

I get this results:
COD_IMM.........COD_RITMI..........SOMMA
02015149............01...............9,2
02015149............04...............14,15
02015149............05...............330,99
02015149............06...............388,17
00000050............01...............3513,98
00000050............04...............4127,99
00000050............05...............3593,51

view test4 is:
COD_IMM........COD_RITMI.......TIPO_SPAZIO........TOTAL_AREA
02015149........01...................................................9,2
02015149........04..................................................14,15
02015149........05.................................................292,75
02015149........06.................................................388,17
00000050........01................................................3513,98
00000050........04...............................................4127,99
00000050........05.................................................2225,83
02015149...................................V.........................38,24
00000050...................................V........................1367,68

max_test1conta is:
COD_IMM........COD_RITMI........MAX_RITMI.........TOTAL_AREA
02015149........01........................2........................9,2
02015149........04........................1........................14,15
02015149........05........................3........................164,77
02015149........06.........................3........................384,39
00000050........01........................12........................1106,04
00000050........04.........................2........................3704,69
00000050........05........................12........................1801

In this case for cod_imm=02015149 max(max_ritmi)=3 and max(total_area) between 05 and 06 =384,39 (cod_ritmi=06)
for cod_imm=00000050 max(max_ritmi)=12 and max(total_area) between 01 and 05 =1801 (cod_ritmi=05)

Results:
COD_IMM.........COD_RITMI..........SOMMA
02015149.........01..................9,2
02015149.........04..................14,15
02015149.........05..................292,75
02015149.........06..................426,41
00000050.........01..................3513,98
00000050.........04..................4127,99
00000050.........05..................2225,83+1367,68=3593,51

I tried also this:
CREATE OR REPLACE VIEW TEST_FINALCONTA ( COD_IMM,
COD_RITMI, SOMMA ) AS select cod_imm,
cod_ritmi,
SUM(total_area) somma
from (select cod_imm,
(CASE WHEN e.cod_ritmi IS NOT NULL
THEN e.cod_ritmi
ELSE (select c.cod_ritmi
from (select cod_imm, cod_ritmi, max_ritmi
from max_test1conta a
where max_ritmi in (select max(max_ritmi)
from max_test1conta where cod_imm = a.cod_imm
)
) b,
max_test1conta c
where total_area = (select max(total_area)
from max_test1conta
where cod_imm = b.cod_imm)
and c.cod_imm = e.cod_imm
and c.cod_ritmi = b.cod_ritmi
)
END) cod_ritmi,
tipo_spazio,
total_area
from test4 e)
group by cod_imm, cod_ritmi

But not seem correct.

How can I modify your query??

Thank you very much, but I think that this query is very difficult.

[balajiyes]

The query and its results are given below:
select cod_imm, cod_ritmi, sum(total_area)
from
(select cod_imm, cod_ritmi,
sum(total_area) total_area from TEST4
where cod_ritmi is not null
group by
cod_imm, cod_ritmi
union all
(select c.cod_imm, d.cod_ritmi,
sum(c.total_area) total_area from
TEST4 c,
(select a.cod_imm, a.cod_ritmi
from MAX_TEST1CONTA a
where a.TOTAL_AREA = (select max(b.TOTAL_AREA) from MAX_TEST1CONTA b
where b.cod_imm = a.cod_imm)) d
where c.cod_ritmi is null
and d.cod_imm = c.cod_imm
group by c.cod_imm, d.cod_ritmi))
group by cod_imm, cod_ritmi

COD_IMM COD_RITMI SUM(TOTAL_AREA)
------- ---------- ---------------
2015149 1 9
2015149 4 14
2015149 5 292
2015149 6 416

Is this what you are looking for?

By the way, the data in your earlier post seems to be wrong - the total_area column in the last row of test4 view should read as 27 and not 38.24. Refer the results below:
SQL> select * from test4;

COD_IMM COD_RITMI T TOTAL_AREA
---------- ---------- - ----------
2015149 1 9
2015149 4 14
2015149 5 292
2015149 6 389
2015149 V 27