oracle views (very difficult)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: oracle views (very difficult)

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    oracle views (very difficult)

    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!!!!

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
    Cheers!
    OraKid.

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    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.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width