Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4

    Unanswered: View that merges data from two tables

    Dear ladies and gentlemen,

    I have got a problem and can not solve it. Therefore, I would be grateul for your suggestions.

    I have two tables:

    Master table "pool" contains data:
    Code:
    id name
    -- ----
    1  mike 
    2  juan
    Detail table "pool_st" contains data:
    Code:
    id des  val
    -- ---  ---
    1  rvt  100
    1  rnt  200
    2  rvt  300
    2  rnt  350
    2  jvt  230
    Is it possible to create a view which would "merge" those tables and show all data (per ID) in one line? Such as:
    Code:
    id name des val des val des val
    -- ---- --- --- --- --- --- ---
    1  mike rvt 100 rnt 200
    2  juan rvt 300 rnt 350 jvt 230
    Thank you!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: View that merges data from two tables

    It depends. Is there a fixed number of columns in this view, or does it depend on what is in the tables? Views, like tables, cannot have a variable column list.

    If the only "des" values were rvt, rnt and jvt then a view could be built like this:
    Code:
    create or replace view v as
    select pool.id, pool.name,
             sum(decode(pool_st.des,'rvt',pool_st.val,0)) rvt_val,
             sum(decode(pool_st.des,'rnt',pool_st.val,0)) rnt_val,
             sum(decode(pool_st.des,'jvt',pool_st.val,0)) jvt_val
    from  pool, pool_st
    where pool.id = pool_st.id
    group by pool.id, pool.name;
    Obviously, this could be extended to any finite list of des values - but those values would have to be known when the view was built.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you, Mr. Andrews!

    Your view works just fine for me - there is finite and known list of "des" values (although you couldn't tell so from my example).

    If you ever come to Croatia, let me know and I believe we'll just have to have another drink in the name of the view

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Littlefoot
    If you ever come to Croatia, let me know and I believe we'll just have to have another drink in the name of the view
    Don't think I won't take you up o that

  5. #5
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: View that merges data from two tables

    I think you should be able to solve this problem by using user-defined aggregate function. In that way you don't have to use hard-coded decode. Here is a link of a silimar post:

    http://asktom.oracle.com/pls/ask/f?p...2196162600402,


    Originally posted by Littlefoot
    Dear ladies and gentlemen,

    I have got a problem and can not solve it. Therefore, I would be grateul for your suggestions.

    I have two tables:

    Master table "pool" contains data:
    Code:
    id name
    -- ----
    1  mike 
    2  juan
    Detail table "pool_st" contains data:
    Code:
    id des  val
    -- ---  ---
    1  rvt  100
    1  rnt  200
    2  rvt  300
    2  rnt  350
    2  jvt  230
    Is it possible to create a view which would "merge" those tables and show all data (per ID) in one line? Such as:
    Code:
    id name des val des val des val
    -- ---- --- --- --- --- --- ---
    1  mike rvt 100 rnt 200
    2  juan rvt 300 rnt 350 jvt 230
    Thank you!

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: View that merges data from two tables

    Link got messed up.

    Please go to asktom.oracle.com
    and search stragg.


    Originally posted by lynden.zhang
    I think you should be able to solve this problem by using user-defined aggregate function. In that way you don't have to use hard-coded decode. Here is a link of a silimar post:

    http://asktom.oracle.com/pls/ask/f?p...2196162600402,

Posting Permissions

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