Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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_AR EA"
    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Raf, a suggestion for you. I always find your posts too difficult to understand, and give up. One thing that would help is if you used tags to get the examples to format nicely like this:
    Code:
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    rather than this:

    SQL> select * from dept;

    ....DEPTNO.DNAME..........LOC
    ----------.--------------.-------------
    ........10.ACCOUNTING.....NEW.YORK
    ........20.RESEARCH.......DALLAS
    ........30.SALES..........CHICAGO
    ........40.OPERATIONS.....BOSTON

    To do that you can put the [code] and [/ code] tags (no space between the slash and the word "code" there really) around the text. That changes the font to a fixed-width one (like Courier).

    You may find the amount of help you get increases!

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    would the GREATEST and LEAST functions be helpful in this regard?

    I am confused a little so it is hard to determine what your question is.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this perhaps?

    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 = (SELECT MAX(max_ritmi)
    FROM MAX_TEST1CONTA) )
    END) cod_ritmi,
    tipo_spazio,
    total_area
    from test4)
    group by cod_imm, cod_ritmi

Posting Permissions

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