|
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|