This discussion is archived
8 Replies Latest reply: Dec 2, 2012 10:50 PM by ORA-00904 RSS

How to select header group with detailed data in one SQL

ORA-00904 Newbie
Currently Being Moderated
Dear,

I would like to have a little help from you. My goal is to create a view where data must be in appropriate format. In this format i would like to have for each group one header.

I have 3 tables which they ar connected to each other (representing hierachical data where financial plan breaks across different groups).

CRM_PLAN
CRM_PLAN_ID
DATE
AMOUNT

CRM_PLAN_PE
CRM_PLAN_PE_ID
TITLE
AMOUNT
CRM_PLAN_ID (ref key to CRM_PLAN.CRM_PLAN_ID)

CRM_PLAN_MONTH
CRM_PLAN_MONTH_ID
YEAR
MONTH
AMOUNT
CRM_PLAN_PE_ID (foreign key to CRM_PLAN_PE.CRM_PLAN_PE_ID)

Data looks like:

CRM_PLAN
CRM_PLAN_ID | DATE | AMOUNT
1 | 01.01.2012 | 500
2 | 01.02.2012 | 200

CRM_PLAN_PE
CRM_PLAN_PE_ID |TITLE | AMOUNT | CRM_PLAN_ID
1 | Organization 1 |100 | 1
2 | Organization 2 |400 | 1
3 | Organization 3 |200 | 2


CRM_PLAN_MONTH
CRM_PLAN_MONTH_ID | YEAR | MONTH | AMOUNT | CRM_PLAN_PE_ID
1 | 2012| 1 | 60 |1
2 | 2012| 2 | 40 |1
3 | 2012| 1 | 350 |2
4 | 2012| 2 | 50 |2
5 | 2012| 1 | 200 |3


I would like view which would return data like:

MONTH | AMOUNT
Organization 1 |null
1 | 60
2 | 40
Organization 2 | null
1 | 350
2 | 50
Organization 3 | null
1 | 200

The main question is hot to write an sql as source for view to insert before each group a row with title column value from table CRM_PLAN_PE. Is that even possible? Maybe you could suggest me some other method.

View will be used in oracle form where will be possible to edit data - I will arrange that through insted of trigger.

Regards,
Igor

Edited by: user5528050 on 29.11.2012 2:23

Edited by: user5528050 on 29.11.2012 2:24

Edited by: user5528050 on 29.11.2012 2:25
  • 1. Re: How to select header group with detailed data in one SQL
    Hoek Guru
    Currently Being Moderated
    Help us to help you better, by reading #7, 8 and 9 of the (apparently) most underrated but most important part of the SQL and PL/SQL FAQ: {message:id=9360002}

    If you post CREATE TABLE + INSERT INTO statements and your database version, we:
    - have a testcase we can run on our databases, so your answers will be tested before posted.
    - do not have any doubts regarding datatypes you use
    - know what built-in functionality we can or cannot use
    etc..

    It is explained in more detail here:
    http://tkyte.blogspot.nl/2005/06/how-to-ask-questions.html
  • 2. Re: How to select header group with detailed data in one SQL
    ORA-00904 Newbie
    Currently Being Moderated
    I have a lot more tables and columns in structure. I just write an example so experts could point me to solution - it doesn't have to be an real and tested SQL stametement.

    Regards,
    Igor
  • 3. Re: How to select header group with detailed data in one SQL
    Hoek Guru
    Currently Being Moderated
    I just write an example so experts could point me to solution
    Posting an example, small yet concise, is a very good plan, don't get me wrong.
    However, real experts are experts because they prefer to TEST their ideas, before posting or only describing a 'guess' that will not/may not help you at all in the end.
    Regarding your example, keep in mind that many experts will want to eliminate any questions/guesses/silly typework, before they will even start working on a solution that will help you.
    - it doesn't have to be an real and tested SQL stametement.
    I do not understand this at all.
    When your code goes to production it is real and tested as well, isn't it?
    When you deliver code to your DBA for implementation, it should be working as well, shouldn't it?
  • 4. Re: How to select header group with detailed data in one SQL
    ORA-00904 Newbie
    Currently Being Moderated
    I don't need written sql just an concept.

    Currently I have new table which I fill with data in appropriate format. If anyone has better solution, please.

    Regards,
    Igor
  • 5. Re: How to select header group with detailed data in one SQL
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Igor,
    user5528050 wrote:
    ... I would like view which would return data like:

    MONTH | AMOUNT
    Organization 1 |null
    1 | 60
    2 | 40
    Organization 2 | null
    1 | 350
    2 | 50
    Organization 3 | null
    1 | 200

    The main question is hot to write an sql as source for view to insert before each group a row with title column value from table CRM_PLAN_PE. Is that even possible? Maybe you could suggest me some other method.
    So, you want to have 2 different kinds of rows in the output:
    (1) "parent" or "master" rows containing data from crm_plan_pe, such as
    MONTH          AMOUNT
    ==============     ======
    Organization 1
    (2) "child" or "detail" rows containing data from crm_plan_month, such as
    MONTH          AMOUNT
    ==============     ======
    1          60
    Sure, you can do that.

    One way is to do a UNION of 2 separate queries: (1) to generate just the parent rows, (2) to generate just the child rows.
    You could also join the 2 tables and use GROUP BY GROUPING SETS. The super-aggregate rows would be the parent rows.


     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved (it looks like crm_plan is not involved in this problem), and also post the results you want from that data. You need to post this whether or not you want SQL code in reply. The people who want to help you want to provide good answers, and that means they want to re-create the probelm and try their ideas.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 6. Re: How to select header group with detailed data in one SQL
    ORA-00904 Newbie
    Currently Being Moderated
    I am giving you a verry short example which must return output
    Organization1    100 
    User 1              60
    User 2               40
    
    Organization 2   200
    User 3              200
    Tables and data:
    -- Create table
    create table TAB1
    (
      id    NUMBER not null,
      title VARCHAR2(250)
    );
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table TAB1
      add constraint PK_TAB1_ID primary key (ID);
    
    -- Create table
    create table TAB2
    (
      id        NUMBER not null,
      amount    NUMBER(10,3),
      tab1_id   NUMBER,
      user_name VARCHAR2(250)
    );
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table TAB2
      add constraint ID primary key (ID);
    alter table TAB2
      add constraint TAB2_REF foreign key (TAB1_ID)
      references TAB1 (ID);
    
    begin
    insert into tab1
      (id, title)
    values
      (1, 'Organization 1');
    insert into tab1
      (id, title)
    values
      (2, 'Organization 2');
      
    insert into tab2
      (id, amount, tab1_id, user_name)
    values
      (1, 60, 1, 'User 1');
    
    insert into tab2
      (id, amount, tab1_id, user_name)
    values
      (2, 40, 1, 'User 2');
      
    insert into tab2
      (id, amount, tab1_id, user_name)
    values
      (3, 200, 2, 'User 3');
    commit;
    end;
    Version: 11.2.0.3.0

    Regards,
    Igor

    Edited by: user5528050 on 30.11.2012 9:47
  • 7. Re: How to select header group with detailed data in one SQL
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Igor,

    In this case, I think ROLLUP is a little simpeler than GROUPING SETS, but you could do it either way:
    SELECT       CASE
               WHEN  GROUPING (tab2.id) = 0
               THEN  TO_CHAR (tab2.id)
               ELSE  tab1.title
           END               AS col_1
    ,       SUM (tab2.amount)     AS col_2
    FROM       tab1
    JOIN       tab2     ON  tab1.id     = tab2.tab1_id
    GROUP BY  tab1.title
    ,            ROLLUP (tab2.id)
    ORDER BY  tab1.title
    ,         GROUPING (tab2.id)     DESC
    ,            tab2.id          
    ;
    If tab2.id can not be NULL, then you can use
    NVL ( TO_CHAR (tab2.id)
        , tab1.title
        )
    instead of the CASE expression, and simplify the ORDER BY clause to
    ORDER BY  tab1.title
    ,            tab2.id            NULLS FIRST          
  • 8. Re: How to select header group with detailed data in one SQL
    ORA-00904 Newbie
    Currently Being Moderated
    Excellent. Thanks.

    Regards,
    Igor

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points