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

    Unanswered: select with UNION

    I've table PUL_PM:

    Code:
    COD_IMM       PIANO     COD_RITMI     TIPO_SPAZIO      AREA
    04602961        PTE                      V             2,82
    04602961	PTE                      V             12,77
    04602961	PT                       O             100,037
    04602961	PTE                      V             6,49
    04602961	PTE       01                           56,409
    04602961	PTE                      O             117,63
    04602961	PTE       01                           25,6
    04602961	PTE       04                           470,52
    04602961	PTE       01                           140,98
    04602961	PTE       05                           39,66
    04602961	PTE       05                           53,18
    04602961	PTE       01                           41,03
    04602961	P01                      O             0,99
    04602961	P01                      O             3,74
    04602961	P01       01                           52,38
    04602961	P01                      V             7,52

    max_tab

    Code:
    COD_IMM        PIANO   COD_RITMI      TOTAL_AREA      MAX_AREA
    04602961       PTE        01            264,019         264,019
    04602961       P01        01             52,38          52,38

    CREATE OR REPLACE VIEW COD_MAX (COD_IMM,
    PIANO, COD_RITMI, TIPO_SPAZIO, AREA
    ) 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
    FROM pul_pm bl, max_tab max
    WHERE bl.cod_imm(+) = max.cod_imm
    AND bl.piano(+) = max.piano

    Code:
    COD_IMM        PIANO   COD_RITMI   TIPO_SPAZIO	AREA
    04602961	PTE	01          O           117,63
    04602961	PTE	01                      41,03
    04602961	PTE	05                      53,18
    04602961	PTE	04                      470,52
    04602961	PTE	01                      25,6
    04602961	PTE	05                      39,66
    04602961	PT                  V           2,82
    04602961	PTE	01          O           100,037
    04602961	PTE	01                      56,409
    04602961	PTE                 V           6,49
    04602961	PTE                 V           12,77
    04602961	PTE     01                      140,98
    04602961	P01	01          O           0,99
    04602961	P01                 V           7,52
    04602961	P01     01          O           3,74
    04602961	P01	01                      52,38
    Now I'd like to delete records with cod_ritmi='04' and add after with union
    In this Case:
    Code:
    04602961	PTE	04                      470,52
    I tried this:

    CREATE OR REPLACE VIEW COD_MAX ( COD_IMM,
    PIANO, COD_RITMI, TIPO_SPAZIO, AREA
    ) AS SELECT bl.cod_imm,
    bl.piano,
    (CASE WHEN tipo_spazio = 'O'
    THEN max.cod_ritmi
    WHEN BL.COD_RITMI='04' THEN null
    ELSE bl.cod_ritmi
    END) cod_ritmi,
    bl.tipo_spazio,
    bl.area
    FROM pul_pm bl, max_tab max
    WHERE bl.cod_imm(+) = max.cod_imm
    AND bl.piano(+) = max.piano
    UNION ALL
    SELECT bl.cod_imm,
    bl.piano,
    BL.cod_ritmi,
    bl.tipo_spazio,
    bl.area
    FROM pu_pm bl
    WHERE BL.COD_RITMI IN ('04')

    but I get 2 records with cod_ritmi='04':
    Code:
    04602961	PTE	04                      470,52
    04602961	PTE	                        470,52
    How Can I get just records with cod_ritmi='04' from second select and no record with cod_ritmi='04' from first select?

    Thanks in advance!

  2. #2
    Join Date
    Oct 2004
    Posts
    9
    Hi raf,
    Personally I do not understand what you mean saying "Now I'd like to delete records with cod_ritmi='04' and add after with union".
    Please clarify and also note that after I reproduced your case in my Oracle, your last select, actually the view) retrieves much more than two rows.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by parisss
    Hi raf,
    Personally I do not understand what you mean saying "Now I'd like to delete records with cod_ritmi='04' and add after with union".
    Please clarify and also note that after I reproduced your case in my Oracle, your last select, actually the view) retrieves much more than two rows.
    sorry,
    I said this:
    In first select, I'd like to get all records without cod_ritmi='04'
    In second select (union) I'd like just record with cod_ritmi='04'

    sum(area) from table PUL_PM = sum(area) from view COD_MAX

    THANKS

  4. #4
    Join Date
    Oct 2004
    Posts
    9
    Hi raf,

    Try the following:
    [
    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
    FROM pul_pm bl, max_tab max
    WHERE bl.cod_imm = max.cod_imm(+)
    AND bl.piano = max.piano(+)
    and NVL(bl.cod_ritmi,'00') != '04'
    union
    SELECT
    bl.cod_imm, bl.piano, BL.cod_ritmi, bl.tipo_spazio, bl.area
    FROM pul_pm bl
    WHERE BL.COD_RITMI = '04'
    /
    ]
    Please note that I changed the plase of the (+) directive. From what I read in your text, I concluded that the "superset" is the table pul_pm, not the max_tab. If this is not the case, just restore the (+) to their original location.

    Now if you want the '04' row to be explicitly the last one shown in the result set, use the following statement:
    [
    SELECT
    1, 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
    FROM pul_pm bl, max_tab max
    WHERE bl.cod_imm = max.cod_imm(+)
    AND bl.piano = max.piano(+)
    and NVL(bl.cod_ritmi,'00') != '04'
    union
    SELECT
    2, bl.cod_imm, bl.piano, BL.cod_ritmi, bl.tipo_spazio, bl.area
    FROM pul_pm bl
    WHERE BL.COD_RITMI = '04'
    order by 1
    /
    ]

    I hope this helps.

Posting Permissions

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