1 Reply Latest reply: Nov 3, 2011 3:35 PM by Frank Kulash RSS

    Creating a tiered report

    897503
      Hi, I'm relatively new to SQL*Plus and trying to figure things out for myself, though a colleague recommended this forum. Apologies for diving straight in with a query!!

      I'm trying to develop a report in the following format

      Header - Hardcoded text
      Shipment Info - From two separate tables (ORDER_HEADER, CARRIER, - CARRIER_ID links the two). Some hardcoded text also included.
      Parcel Info - From two further tables (V_PARCEL, V_CARTON_TYPE Order_ID links V_PARCEL to ORDER_HEADER and CARTON_TYPE links the two tables). Some hardcoded text also included.

      So my report should ultimately look something like

      HeaderText
      Shipment1
      ParcelA
      ParcelB
      ParcelC
      Shipment2
      ParcelD
      Shipment3
      ParcelE
      ParcelF

      So far I have created a single SQL statement which picks up all the information I require but repeats all the information to the smallest degree...

      HeaderText Shipment1 ParcelA
      HeaderText Shipment1 ParcelB
      HeaderText Shipment1 ParcelC
      HeaderText Shipment2 ParcelD
      .....

      Can I please beg for advice on how best to reformat / rewrite my query into the correct structure.

      Forgive me for including my current query...



      select 'FF0HDR|FFTIN00300184073195 DHLEUAPGW ',
      'FF0SHP|2184073195 ',
      (ORDER_HEADER.V_INV_ZPRO_TEXT),
      (ORDER_HEADER.V_INV_ZNET_TEXT)||'| | GB A||',
      (ORDER_HEADER.CUSTOMER_ID),
      (ORDER_HEADER.NAME)||' | |'||
      (ORDER_HEADER.ADDRESS1),
      (ORDER_HEADER.ADDRESS2),
      (ORDER_HEADER.TOWN),
      (ORDER_HEADER.COUNTY),
      (ORDER_HEADER.POSTCODE),
      (ORDER_HEADER.CONTACT_PHONE),
      (ORDER_HEADER.CONTACT_FAX),
      (ORDER_HEADER.COUNTRY),
      (CARRIERS.V_SERV_DESIGNATOR),
      (ORDER_HEADER.V_TOTAL_CARTONS),
      (ORDER_HEADER.V_BOX_EXCESS),
      round(ORDER_HEADER.ORDER_WEIGHT),
      round(ORDER_HEADER.ORDER_VOLUME),
      '*DVFC*',
      'Printed Matter ',
      'P',
      ' ',
      ' ',
      ' ',
      ' ',
      ' ',
      'FF0SRV ',
      'FF0PCE ',
      round(V_CARTON_TYPE.DEPTH),
      round(V_CARTON_TYPE.WIDTH),
      round(V_CARTON_TYPE.HEIGHT),
      'CMT',
      round(V_PARCEL.V_CARTONWEIGHT),
      'KGM3',
      ' ',
      'JJD011012345',
      SUBSTR(V_PARCEL.V_PARCEL_ID,3,9),
      ' '
      from ORDER_HEADER,CARRIERS,V_PARCEL,V_CARTON_TYPE
      where ORDER_HEADER.CARRIER_ID = CARRIERS.CARRIER_ID
      and ORDER_HEADER.ORDER_ID = V_PARCEL.ORDER_ID
      and V_PARCEL.V_CARTON_TYPE = V_CARTON_TYPE.V_TYPE
      and (ORDER_HEADER.ORDER_ID LIKE '0087107790')
      ;




      Thanks in advance for any help...

      Edited by: 894500 on 02-Nov-2011 09:38
        • 1. Re: Creating a tiered report
          Frank Kulash
          Hi,

          Welcome to the forum!
          894500 wrote:
          Hi, I'm relatively new to SQL*Plus and trying to figure things out for myself, though a colleague recommended this forum.
          Your colleague might have meant the nearby SQL and PL/SQL forum:
          SQL and PL/SQL
          This forum is specifically for questions relating to the SQL*Plus and iSQL interfaces, that is, things like the SPOOL command. Of course, it's not always obvious whether a given problem is a SQL*Plus problem or a SQL problem, but, when in doubt, I suggest posting things in the SQL and PL/SQL forum, above.
          It's possible that this question can be solved with the SQL*Plus BREAK command (see below), but it's more likely that you need GROUP BY ROLLUP or CONNECT BY, which are SQL features and have nothing to do with SQL*Plus.
          Apologies for diving straight in with a query!!
          No need; that's what the forum is for!
          I'm trying to develop a report in the following format

          Header - Hardcoded text
          Shipment Info - From two separate tables (ORDER_HEADER, CARRIER, - CARRIER_ID links the two). Some hardcoded text also included.
          Parcel Info - From two further tables (V_PARCEL, V_CARTON_TYPE Order_ID links V_PARCEL to ORDER_HEADER and CARTON_TYPE links the two tables). Some hardcoded text also included.
          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statemens, relevant columns only) and the results you want from that data. Explain how you get those results from that data.
          Always say which version of Oracle you're using.
          So my report should ultimately look something like

          HeaderText
          Shipment1
          ParcelA
          ParcelB
          ParcelC
          Shipment2
          ParcelD
          Shipment3
          ParcelE
          ParcelF
          You may have noticed that this site normally doesn't display multiple spaces in a row.
          Whenever you post formatted text (such as query results) on this site, type these 6 characters:

          \
          (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
          
          So far I have created a single SQL statement which picks up all the information I require but repeats all the information to the smallest degree...
          Sorry, I'm not sure what you mean by "repeats all the information to the smallest degree".  It might help if you posted your current results, and explained what's wrong with them.
          HeaderText Shipment1 ParcelA
          HeaderText Shipment1 ParcelB
          HeaderText Shipment1 ParcelC
          HeaderText Shipment2 ParcelD
          .....

          Can I please beg for advice on how best to reformat / rewrite my query into the correct structure.

          Forgive me for including my current query...
          Don't be redic!  It's very helpful to show your current query. Thanks for including it.
          select 'FF0HDR|FFTIN00300184073195 DHLEUAPGW ',
          'FF0SHP|2184073195 ',
          (ORDER_HEADER.V_INV_ZPRO_TEXT),
          (ORDER_HEADER.V_INV_ZNET_TEXT)||'| | GB A||',
          (ORDER_HEADER.CUSTOMER_ID),
          (ORDER_HEADER.NAME)||' | |'||
          (ORDER_HEADER.ADDRESS1),
          (ORDER_HEADER.ADDRESS2),
          (ORDER_HEADER.TOWN),
          (ORDER_HEADER.COUNTY),
          (ORDER_HEADER.POSTCODE),
          (ORDER_HEADER.CONTACT_PHONE),
          (ORDER_HEADER.CONTACT_FAX),
          (ORDER_HEADER.COUNTRY),
          (CARRIERS.V_SERV_DESIGNATOR),
          (ORDER_HEADER.V_TOTAL_CARTONS),
          (ORDER_HEADER.V_BOX_EXCESS),
          round(ORDER_HEADER.ORDER_WEIGHT),
          round(ORDER_HEADER.ORDER_VOLUME),
          '*DVFC*',
          'Printed Matter ',
          'P',
          ' ',
          ' ',
          ' ',
          ' ',
          ' ',
          'FF0SRV ',
          'FF0PCE ',
          round(V_CARTON_TYPE.DEPTH),
          round(V_CARTON_TYPE.WIDTH),
          round(V_CARTON_TYPE.HEIGHT),
          'CMT',
          round(V_PARCEL.V_CARTONWEIGHT),
          'KGM3',
          ' ',
          'JJD011012345',
          SUBSTR(V_PARCEL.V_PARCEL_ID,3,9),
          ' '
          from ORDER_HEADER,CARRIERS,V_PARCEL,V_CARTON_TYPE
          where ORDER_HEADER.CARRIER_ID = CARRIERS.CARRIER_ID
          and ORDER_HEADER.ORDER_ID = V_PARCEL.ORDER_ID
          and V_PARCEL.V_CARTON_TYPE = V_CARTON_TYPE.V_TYPE
          and (ORDER_HEADER.ORDER_ID LIKE '0087107790')
          ;
          Your query helps, but without sany sample data, and clearly formatted results, I'm not sure exacly what the problem is. I think your problem is similar to this: You have tables arranged in a sort of hierarchy or parent-child relationship.  In the scott schema, there are similar relationships: you might say that the dept table is a parent of the emp table, because every row in emp belongs to one of the deptartments in dept.  You might also say that emp is a child of salgrade, because every row in emp belongs to one of the grades in salgrade.  Say you wanted to show the various departments, the various salgrades found in each department, and the severl employees in each salgrade.  You might wriote a query like this:
          SELECT     d.dname
          ,     'Grade'     AS g
          ,     s.grade
          ,     'Emp'          AS e
          ,     e.empno
          ,     e.sal
          FROM     scott.dept     d
          JOIN     scott.emp     e ON d.deptno = e.deptno
          JOIN     scott.salgrade s ON     e.sal     BETWEEN     s.losal
                                   AND          s.hisal
          ORDER BY d.dname
          ,      s.grade
          ,     e.ename
          ;
          which produces this output:
          DNAME G GRADE E EMPNO SAL
          -------------- ----- ---------- --- ---------- ----------
          ACCOUNTING Grade 2 Emp 7934 1300
          ACCOUNTING Grade 4 Emp 7782 2450
          ACCOUNTING Grade 5 Emp 7839 5000
          RESEARCH Grade 1 Emp 7876 1100
          RESEARCH Grade 1 Emp 7369 800
          RESEARCH Grade 4 Emp 7902 3000
          RESEARCH Grade 4 Emp 7566 2975
          RESEARCH Grade 4 Emp 7788 3000
          SALES Grade 1 Emp 7900 950
          SALES Grade 2 Emp 7654 1250
          SALES Grade 2 Emp 7521 1250
          SALES Grade 3 Emp 7499 1600
          SALES Grade 3 Emp 7844 1500
          SALES Grade 4 Emp 7698 2850
          That's perfectly accurate, but it can be hard to read because some of the data is repeated over and over.  Since we're using an ORDER BY clause, we might prefer to have some of columns not display when they're the same as the preceding row; like this:
          DNAME G GRADE E EMPNO SAL
          -------------- ----- ---------- --- ---------- ----------
          ACCOUNTING Grade 2 Emp 7934 1300
          4 Emp 7782 2450
          5 Emp 7839 5000
          RESEARCH Grade 1 Emp 7876 1100
          Emp 7369 800
          4 Emp 7902 3000
          Emp 7566 2975
          Emp 7788 3000
          SALES Grade 1 Emp 7900 950
          2 Emp 7654 1250
          Emp 7521 1250
          3 Emp 7499 1600
          Emp 7844 1500
          4 Emp 7698 2850
          You can get those results in SQL*Plus by saying
          BREAK ON dname          ON g     on grade
          before running the query.  You don't have to change the query at all.
          
          I hope that answers you're question, but I'm unsure qhat your question really is, so I realize it probably doesn't.  Please do post a little sample data (CREATE TABLE and INSERT statements, maybe 2-10 rows per table) and the formatted results you want from that data.
          If you decide to post this question in the SQL and PL/SQL forum, then please mark this thread as "Answered" when you do; otherwise people will think you're being rude by posting the same question over and over again.