6.7 KiB
6.7 KiB
V_REP_ALLESTIMENTI
Colonne
| Colonna | Tipo |
|---|---|
| D1 | DATE |
| D2 | DATE |
| D3 | DATE |
| D4 | DATE |
| D5 | DATE |
| D6 | DATE |
| D7 | DATE |
| D8 | DATE |
| D9 | DATE |
| D10 | DATE |
| D11 | DATE |
| D12 | DATE |
| L1 | VARCHAR2(200) |
| L2 | VARCHAR2(200) |
| L3 | VARCHAR2(200) |
| L4 | VARCHAR2(200) |
| L5 | VARCHAR2(200) |
| L6 | VARCHAR2(200) |
| L7 | VARCHAR2(200) |
| L8 | VARCHAR2(200) |
| L9 | VARCHAR2(200) |
| L10 | VARCHAR2(200) |
| L11 | VARCHAR2(200) |
| L12 | VARCHAR2(200) |
| T1 | VARCHAR2(4000) |
| T2 | VARCHAR2(4000) |
| T3 | VARCHAR2(4000) |
| T4 | VARCHAR2(4000) |
| T5 | VARCHAR2(4000) |
| T6 | VARCHAR2(4000) |
| T7 | VARCHAR2(4000) |
| T8 | VARCHAR2(4000) |
| T9 | VARCHAR2(4000) |
| T10 | VARCHAR2(4000) |
| T11 | VARCHAR2(4000) |
| T12 | VARCHAR2(4000) |
| C1 | VARCHAR2(4000) |
| C2 | VARCHAR2(4000) |
| C3 | VARCHAR2(4000) |
| C4 | VARCHAR2(4000) |
| C5 | VARCHAR2(4000) |
| C6 | VARCHAR2(4000) |
| C7 | VARCHAR2(4000) |
| C8 | VARCHAR2(4000) |
| C9 | VARCHAR2(4000) |
| C10 | VARCHAR2(4000) |
| C11 | VARCHAR2(4000) |
| C12 | VARCHAR2(4000) |
Definizione
CREATE OR REPLACE VIEW V_REP_ALLESTIMENTI AS
with t as (select e.id, e.data, l.location, e.torta, e.confettata
from eventi e
left join location l on e.id_location = l.id
where data >= sysdate
order by data
)
, q as (select t.* , rownum as order_id
from t
order by data
)
, r as (select -- q.* ,
case when order_id =1 then data else null end as data1,
case when order_id =2 then data else null end as data2,
case when order_id =3 then data else null end as data3,
case when order_id =4 then data else null end as data4,
case when order_id =5 then data else null end as data5,
case when order_id =6 then data else null end as data6,
case when order_id =7 then data else null end as data7,
case when order_id =8 then data else null end as data8,
case when order_id =9 then data else null end as data9,
case when order_id =10 then data else null end as data10,
case when order_id =11 then data else null end as data11,
case when order_id =12 then data else null end as data12,
case when order_id =1 then location else null end as location1,
case when order_id =2 then location else null end as location2,
case when order_id =3 then location else null end as location3,
case when order_id =4 then location else null end as location4,
case when order_id =5 then location else null end as location5,
case when order_id =6 then location else null end as location6,
case when order_id =7 then location else null end as location7,
case when order_id =8 then location else null end as location8,
case when order_id =9 then location else null end as location9,
case when order_id =10 then location else null end as location10,
case when order_id =11 then location else null end as location11,
case when order_id =12 then location else null end as location12,
case when order_id =1 then torta else null end as torta1,
case when order_id =2 then torta else null end as torta2,
case when order_id =3 then torta else null end as torta3,
case when order_id =4 then torta else null end as torta4,
case when order_id =5 then torta else null end as torta5,
case when order_id =6 then torta else null end as torta6,
case when order_id =7 then torta else null end as torta7,
case when order_id =8 then torta else null end as torta8,
case when order_id =9 then torta else null end as torta9,
case when order_id =10 then torta else null end as torta10,
case when order_id =11 then torta else null end as torta11,
case when order_id =12 then torta else null end as torta12,
case when order_id =1 then confettata else null end as confettata1,
case when order_id =2 then confettata else null end as confettata2,
case when order_id =3 then confettata else null end as confettata3,
case when order_id =4 then confettata else null end as confettata4,
case when order_id =5 then confettata else null end as confettata5,
case when order_id =6 then confettata else null end as confettata6,
case when order_id =7 then confettata else null end as confettata7,
case when order_id =8 then confettata else null end as confettata8,
case when order_id =9 then confettata else null end as confettata9,
case when order_id =10 then confettata else null end as confettata10,
case when order_id =11 then confettata else null end as confettata11,
case when order_id =12 then confettata else null end as confettata12
from q
)
select min(data1) as d1,
min(data2) as d2,
min(data3) as d3,
min(data4) as d4,
min(data5) as d5,
min(data6) as d6,
min(data7) as d7,
min(data8) as d8,
min(data9) as d9,
min(data10) as d10,
min(data11) as d11,
min(data12) as d12,
min(location1) as l1,
min(location2) as l2,
min(location3) as l3,
min(location4) as l4,
min(location5) as l5,
min(location6) as l6,
min(location7) as l7,
min(location8) as l8,
min(location9) as l9,
min(location10) as l10,
min(location11) as l11,
min(location12) as l12,
min(torta1) as t1,
min(torta2) as t2,
min(torta3) as t3,
min(torta4) as t4,
min(torta5) as t5,
min(torta6) as t6,
min(torta7) as t7,
min(torta8) as t8,
min(torta9) as t9,
min(torta10) as t10,
min(torta11) as t11,
min(torta12) as t12,
min(confettata1) as c1,
min(confettata2) as c2,
min(confettata3) as c3,
min(confettata4) as c4,
min(confettata5) as c5,
min(confettata6) as c6,
min(confettata7) as c7,
min(confettata8) as c8,
min(confettata9) as c9,
min(confettata10) as c10,
min(confettata11) as c11,
min(confettata12) as c12
from r