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

    Unanswered: create an Oracle View..

    Hi,
    I've this table

    TAB1:

    COD_M..................COD_R....................CO D_MAT
    00114940.................1......................00 114940
    00114949.................3
    00114947.................1
    00116220.................1......................00 116220
    00116220.................3
    00116300.................1......................00 116300
    00116990.................1......................00 116300
    00119320.................1......................00 119320
    00119320.................3
    00119324.................3
    00119450.................1......................00 119450


    TAB2:
    COD_M
    00114940
    00114949
    00116220
    00116300
    00116500
    00116980
    00116990
    00119320
    00119450
    00119324

    COD_MAT 00119450 has 1 COD_M

    COD_M 00114949 has COD_MAT null with COD_R=3 (In this case I must count=0)

    COD_M 00114947 has COD_MAT null but with COD_R=1 (In this case I don't have to consider it)

    COD_MAT 00116220 has 1 COD_M

    COD_MAT 00116300 has 2 COD_M

    COD_M 00119324 has COD_MAT null with COD_R=3 (In this case I must count=0)

    COD_MAT 00119450 has 1 COD_M

    I must consider only cod_m that are in the table TAB2

    I'd like to get this output:

    COD_M..................count_mat
    00114940...................1
    00114949...................0
    00116220...................1
    00116300...................2
    00119324...................0
    00119450...................1

    con this query i get only count (without zero)

    SELECT TAB2.CODE_M ,
    COUNT(TAB1.CODE_MATRICOLA)
    FROM TAB2,
    TAB1
    WHERE TAB2.CODE_M = TAB1.CODE_MAT
    AND TAB1.CODE_R IN ('1','3')
    GROUP BY TAB2.CODE_M

    How Can I create an Oracle view to get this output?
    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use an outer join:
    Code:
    SELECT TAB2.CODE_M ,
    COUNT(TAB1.CODE_MATRICOLA)
    FROM TAB2,
    TAB1
    WHERE TAB2.CODE_M = TAB1.CODE_MAT (+)
    AND TAB1.CODE_R (+) IN ('1','3')
    GROUP BY TAB2.CODE_M

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by andrewst
    Use an outer join:
    Code:
    SELECT TAB2.CODE_M ,
    COUNT(TAB1.CODE_MATRICOLA)
    FROM TAB2,
    TAB1
    WHERE TAB2.CODE_M = TAB1.CODE_MAT (+)
    AND TAB1.CODE_R (+) IN ('1','3')
    GROUP BY TAB2.CODE_M
    I tried this
    SELECT TAB2.COD_M ,
    COUNT(TAB1.COD_MAT) count_mat
    FROM TAB2,
    TAB1
    WHERE TAB2.COD_R (+) = TAB1.COD_MAT
    AND TAB1.COD_R IN ('1','3')
    GROUP BY TAB2.COD_M

    but I get this output:

    COD_M..................count_mat
    00114940...................1
    00114949...................0
    00116220...................1
    00116300...................2
    00119324...................0
    00119450...................1
    ................................0

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ... which is pretty close to what you asked for, isn't it? Just add:

    AND TAB2.COD_M IS NOT NULL

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by andrewst
    ... which is pretty close to what you asked for, isn't it? Just add:

    AND TAB2.COD_M IS NOT NULL
    sorry,
    with the query
    SELECT TAB2.COD_M ,
    COUNT(TAB1.COD_MAT) count_mat
    FROM TAB2,
    TAB1
    WHERE TAB2.COD_R (+) = TAB1.COD_MAT
    AND TAB1.COD_R IN ('1','3')
    GROUP BY TAB2.COD_M

    I get:

    COD_M..........COUNT_MAT
    00114940...........1
    00116220...........1
    00116300...........2
    00119320...........1
    00119450...........1
    ...................0

    but I'd like to get this output:

    COD_M..................count_mat
    00114940...................1
    00114949...................0
    00116220...................1
    00116300...................2
    00119324...................0
    00119450...................1

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What happens when you try the code I posted, which is different to what you are trying? Outer join is on other side.

Posting Permissions

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