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

    Unanswered: How to get sum(area) with null values

    Hi

    I've table SK:

    Code:
    COD_IMM..........DESCR_R.......COD_R
    01049171.........Centr Pres.....06

    and table REP_SK

    Code:
    COD_IMM..........DESCR_R.......COD_R............AREA
    01049171.........Autor..........04.............24,0036942057317
    01049171.........Centr Pres.....05.............391,015086878023
    01049171.........Centr Dep.....................379,981218916245

    I'd like to get a view with these results:

    391,015086878023+379,981218916245=770,996305794268

    Code:
    COD_IMM..........DESCR_R.......COD_R............AREA
    01049171.........Centr Pres.....06.............770,996305794268
    01049171.........Autor..........04.............24,0036942057317
    all cod_imm with cod_r='04' don't must be included in the sum(area)

    I tried this:
    CREATE OR REPLACE VIEW REP_SK_VIEW (COD_IMM, DESCR_R, COD_R,AREA)
    AS
    select A.COD_IMM,
    B.DESCR_R,
    B.COD_R,
    sum(A.AREA),
    from REP_SK a, SK B
    WHERE A.COD_IMM=B.COD_IMM
    AND A.COD_R NOT IN ('04')
    group by A.COD_IMM,B.DESCR_R,B.COD_R
    UNION ALL
    select A.COD_IMM,
    B.DESCR_R,
    B.COD_R,
    sum(A.AREA),
    from REP_SK a, SK B
    WHERE A.COD_IMM=B.COD_IMM
    AND A.COD_R IN ('04')
    group by A.COD_IMM, B.DESCR_R, B.COD_R


    but I get:

    Code:
    COD_IMM..........DESCR_R.......COD_R............AREA
    01049171.........Centr Pres.....05.............391,015086878023
    01049171.........Autor..........04.............24,0036942057317
    How Can I get also cod_r=null in the sum of the area??

    Thanks in advance!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Instead of:
    Code:
    AND A.COD_R NOT IN ('04')
    Use:
    Code:
    AND NVL(A.COD_R,'x') NOT IN ('04')
    Or better still do it all in one hit like this:
    Code:
    CREATE OR REPLACE VIEW REP_SK_VIEW (COD_IMM, DESCR_R, COD_R,AREA)
    AS
    select A.COD_IMM,
    B.DESCR_R,
    B.COD_R,
    sum(A.AREA),
    from REP_SK a, SK B
    WHERE A.COD_IMM=B.COD_IMM
    group by A.COD_IMM,B.DESCR_R,B.COD_R,
    CASE WHEN A.COD_R = '04' THEN 1 ELSE 2 END

Posting Permissions

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