1 Reply Latest reply: May 16, 2013 5:08 PM by Solomon Yakobson RSS

    Query Help

    user11156570
      Hello Experts !

      I have two tables
      Order Header and Order Lines

      Let us see one test example
      create table order_header
      (order_id int primary key, order_number varchar2(30) , qty decimal(2,0))
      
      insert into order_header
      values
      ('001','XX001',40)
      
      create table order_lines
      (order_id int, order_line_id int primary key , product_name varchar2(20))
      
      INSERT INTO ORDER_LINES
      VALUES
      ('001',1,'COLGATE')
      
      INSERT INTO ORDER_LINES
      VALUES
      ('001',2,'PEPSODENT')
      
      SELECT * FROM ORDER_HEADER A
      INNER JOIN ORDER_LINES B
      ON(A.ORDER_ID = B.ORDER_ID)
      Output is:
      order_id--order_num--qty--order_id--order_line_id--product
      1--------------     XX001-------     40-----1--------------1----------------COLGATE
      1--------------XX001--------40----1--------------     2--------------     PEPSODENT
      I want to add extra custom column which contains [Header and Detail value]

      In above example ! Order 1 have 2 lines ! so output shown 2 rows..

      Header table contains always one row and lines contain multiple rows but In header row the cusom column should have "HEADER" value and rest of the rows related to that
      header should have "LINES" how can I achieve this?

      I am using oracle database 10g
      tool is toad 10.

      Regards,
      ChinTOoo
        • 1. Re: Query Help
          Solomon Yakobson
          Something like:
          SELECT  NVL2(B.ORDER_ID,'LINES','HEADER') CUSTOM,
                  A.*,
                  B.*
            FROM      ORDER_HEADER A
                  INNER JOIN
                      ORDER_LINES B
                    ON(
                       A.ORDER_ID = B.ORDER_ID
                      )
            GROUP BY GROUPING SETS((a.order_id,order_number,qty),(a.order_id,order_number,qty,b.order_id,order_line_id,product_name))
            ORDER BY A.ORDER_ID,
                     B.ORDER_ID NULLS FIRST,
                     B.ORDER_LINE_ID
          /
          CUSTOM   ORDER_ID ORDER_NUMBER                          QTY   ORDER_ID ORDER_LINE_ID PRODUCT_NAME
          ------ ---------- ------------------------------ ---------- ---------- ------------- ------------
          HEADER          1 XX001                                  40
          LINES           1 XX001                                  40          1             1 COLGATE
          LINES           1 XX001                                  40          1             2 PEPSODENT
          
          SQL> 
          Or:
          WITH T AS (
                      SELECT  'LINES' CUSTOM,
                              A.*,
                              B.ORDER_ID LINE_ORDER_ID,
                              B.ORDER_LINE_ID,
                              B.PRODUCT_NAME
                        FROM      ORDER_HEADER A
                              INNER JOIN
                                  ORDER_LINES B
                                ON(
                                   A.ORDER_ID = B.ORDER_ID
                                  )
                     UNION ALL
                      SELECT  'HEADER' CUSTOM,
                              A.*,
                              null,
                              null,
                              null
                        FROM  ORDER_HEADER A
                    )
          SELECT  *
            FROM  T
            ORDER BY ORDER_ID,
                     CUSTOM,
                     ORDER_LINE_ID
          /
          
          CUSTOM   ORDER_ID ORDER_NUMBER                          QTY LINE_ORDER_ID ORDER_LINE_ID PRODUCT_NAME
          ------ ---------- ------------------------------ ---------- ------------- ------------- ------------
          HEADER          1 XX001                                  40
          LINES           1 XX001                                  40             1             1 COLGATE
          LINES           1 XX001                                  40             1             2 PEPSODENT
          
          SQL> 
          SY.