1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to return first row where the output has more than one row with different values

Discussion in 'Oracle Forms and Reports' started by miuco, Mar 26, 2015.

  1. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Just joined the forum. Please help.
     

    Attached Files:

  2. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: how to return first row where the output has more than one row with different val

    I need to display the first row in each group.
     
  3. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: how to return first row where the output has more than one row with different val

    I am using oracle Report Builder 10.1.2.0.2.
     
  4. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: how to return first row where the output has more than one row with different val

    Below is my sql that displays the attached output;

    SELECT DISTINCT(pl.line_num),PL.ITEM_ID,MSI.SEGMENT1 AS ITEM_CODE,PL.ITEM_DESCRIPTION,APS.VENDOR_NAME,APS.VENDOR_ID,MSI.PRIMARY_UOM_CODE AS UOM,
    TO_NUMBER (PH.SEGMENT1) "PO No.",TO_CHAR (PH.CREATION_DATE, 'dd-Mon-yyyy') "PO Date",
    PL2.UNIT_PRICE "Current Price", PL.UNIT_PRICE "Previous Price"
    FROM PO_HEADERS_ALL PH,
    PO_LINES_ALL PL,
    PO_LINES_ALL PL2,
    PO_DISTRIBUTIONS_ALL PD,
    AP_SUPPLIERS APS,
    AP_SUPPLIER_SITES_ALL APSS,
    MTL_SYSTEM_ITEMS_B MSI,
    HR_LOCATIONS_ALL HL,
    HR_ALL_ORGANIZATION_UNITS HAOU,
    FND_TERRITORIES FT,
    AP_TERMS_TL APT
    WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
    ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
    and pl2.creation_date<pl.creation_date
    AND APS.VENDOR_ID = PH.VENDOR_ID
    AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
    AND PD.PO_LINE_ID = PL.PO_LINE_ID
    ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
    AND NVL (PL.CANCEL_FLAG, 'N') = 'N'
    AND APS.VENDOR_ID = APSS.VENDOR_ID
    AND APSS.VENDOR_SITE_ID = PH.VENDOR_SITE_ID
    AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
    AND MSI.INVENTORY_ITEM_ID = PL2.ITEM_ID
    AND APSS.COUNTRY = FT.TERRITORY_CODE
    AND APT.TERM_ID(+) = PH.TERMS_ID
    AND HAOU.LOCATION_ID = HL.LOCATION_ID
    AND HAOU.ORGANIZATION_ID = PL.ORG_ID
    and PH.SEGMENT1=:pO_NO ---15000556--:p_PO_No
    and pl2. last_update_date>pl. last_update_date
    order by pl.line_num--- TO_NUMBER (PH.SEGMENT1) desc
     
  5. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Re: how to return first row where the output has more than one row with different val

    use analytic function:
    Code (SQL):

    SELECT *
    FROM
    (SELECT pl.line_num,
           PL.item_id,
           MSI.segment1                              AS ITEM_CODE,
           PL.item_description,
           APS.vendor_name,
           APS. vendor_id,
           MSI.primary_uom_code                      AS UOM,
           To_number (PH.segment1)                   "PO No.",
           To_char (PH.creation_date, 'dd-Mon-yyyy') "PO Date",
           PL2.unit_price                            "Current Price",
           PL.unit_price                             "Previous Price"
           ROW_NUMBER() OVER(partition BY pl.line_num ORDER BY pl.item_id) rn  --it's up to You by which column to order
    FROM   po_headers_all PH,
           po_lines_all PL,
           po_lines_all PL2,
           po_distributions_all PD,
           ap_suppliers APS,
           ap_supplier_sites_all APSS,
           mtl_system_items_b MSI,
           hr_locations_all HL,
           hr_all_organization_units HAOU,
           fnd_territories FT,
           ap_terms_tl APT
    WHERE  PH.po_header_id = PL.po_header_id
           ---and PH.PO_HEADER_ID = PL2.PO_HEADER_ID
           AND pl2.creation_date < pl.creation_date
           AND APS.vendor_id = PH.vendor_id
           AND PD.po_header_id = PH.po_header_id
           AND PD.po_line_id = PL.po_line_id
           ---- AND PD.PO_LINE_ID = PL2.PO_LINE_ID
           AND Nvl (PL.cancel_flag, 'N') = 'N'
           AND APS.vendor_id = APSS.vendor_id
           AND APSS.vendor_site_id = PH.vendor_site_id
           AND MSI.inventory_item_id = PL.item_id
           AND MSI.inventory_item_id = PL2.item_id
           AND APSS.country = FT.territory_code
           AND APT.term_id(+) = PH.terms_id
           AND HAOU.location_id = HL.location_id
           AND HAOU.organization_id = PL.org_id
           AND PH.segment1 = :PO_NO ---15000556--:P_PO_No
           AND pl2. last_update_date > pl. last_update_date )
    WHERE rn = 1      
    ORDER  BY line_num--- TO_NUMBER (PH.SEGMENT1) desc

     
     
    miuco likes this.
  6. miuco

    miuco Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Re: how to return first row where the output has more than one row with different val

    Thank you so much Eras. This query has given me the exact output I was looking for. Thank you once more.