8 Replies Latest reply: Dec 3, 2012 12:50 AM by ORA-00904 RSS

    How to select header group with detailed data in one SQL

    ORA-00904
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Excellent. Thanks.

                        Regards,
                        Igor