Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Creating a tiered report

    Hi, I'm relatively new to SQL 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...

  2. #2
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Is there a problem of duplicate data? or report format? Please specify

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    Quote Originally Posted by jassi.singh View Post
    Hello,

    Is there a problem of duplicate data? or report format? Please specify
    Hi,

    It is the report format I am trying to achieve.

    So, whilst I am currently getting results like this

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



    What I'm hoping to do is to tier those results so that:

    1. the header text appears once at the top of the page
    2. then a shipment detail is returned
    3. then the details for every parcel in that shipment are returned
    4. then the next shipment detail is returned
    5. then the details for every parcel in that shipment are returned
    6. Repeat for specified range of shipments (where)


  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What tool do you use?

Posting Permissions

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