Results 1 to 2 of 2

Thread: group by

  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: group by

    Hi All,

    I am trying to fectch the values of the date,SO#,LINE#,ITEM#,CUSTOMVER_NAME BY Running the follwoing query

    select min(trunc(oola.request_date))
    -- ,ooha.order_number SO#
    -- ,oola.line_number Line#
    ,msib.segment1||'.'||msib.segment3 Item#
    ,nvl(hcasa.translated_customer_name, hp.party_name) Customer_Name
    -- ,trunc(oola.request_date)
    from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,mtl_system_items_b msib
    ,oe_transaction_types_tl ot
    ,so_lookups sscv
    ,hz_cust_site_uses_all hcsua
    ,hz_cust_acct_sites_all hcasa
    ,hz_parties hp
    ,hz_cust_accounts hca
    ,mtl_item_categories mic
    ,mtl_categories mc
    ,mtl_category_sets mcs
    ,mtl_parameters oog
    ,hz_party_sites hps
    ,oe_hold_sources_all ohs
    ,ccw_alloc_dsoh dsoh
    ,oe_order_holds_all oohlda
    ,wsh_delivery_details wdd
    ,ccw_alloc_lookup cal
    ,fnd_lookup_values fnd
    where ooha.header_id = oola.header_id
    and ooha.order_type_id = ot.transaction_type_id
    and ot.language='US'
    and sscv.lookup_code = ooha.sales_channel_code
    and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    and ooha.ship_to_org_id = hcsua.site_use_id(+)
    and hcsua.cust_acct_site_id= hcasa.cust_acct_site_id(+)
    and ooha.sold_to_org_id = hca.cust_account_id(+)
    and hca.party_id = hp.party_id(+)
    and hcasa.party_site_id = hps.party_site_id(+)
    and oola.inventory_item_id = msib.inventory_item_id
    and mc.category_id = mic.category_id
    and mcs.category_set_id = mic.category_set_id
    and mic.inventory_item_id = msib.inventory_item_id
    and mic.organization_id = msib.organization_id
    and oola.flow_status_code in ('AWAITING_SHIPPING', 'BOOKED', 'PRODUCTION_COMPLETE', 'PRODUCTION_OPEN')
    and mcs.category_set_name = 'Inventory'
    and oola.ship_from_org_id = msib.organization_id
    and oog.organization_id = msib.organization_id
    and oola.inventory_item_id = ohs.hold_entity_id
    and ohs.hold_id = 1026
    and ohs.hold_entity_code = 'I'
    and ohs.released_flag != 'Y'
    and ohs.hold_release_id is null
    and dsoh.dmdunit = msib.segment3
    and dsoh.loc = hps.party_site_number
    -- and oohlda.hold_source_id = ohs.hold_source_id
    and oohlda.hold_release_id is null
    and oohlda.header_id = oola.header_id
    and oohlda.line_id = oola.line_id
    and wdd.released_status != 'Y'
    and wdd.source_header_id = oola.header_id
    and wdd.source_line_id = oola.line_id
    and wdd.date_scheduled is null
    and cal.location_id(+) = hps.party_site_number
    and upper(sscv.meaning) = fnd.lookup_code(+)
    and fnd.lookup_type(+) = 'CCW_ALL_SALES_CHANNEL_CODE'
    and sscv.lookup_type='SALES_CHANNEL'
    and msib.segment3 = '63535'
    group by msib.segment1||'.'||msib.segment3
    ,nvl(hcasa.translated_customer_name, hp.party_name)
    -- ,ooha.order_number
    -- ,oola.line_number



    UNION
    select min(trunc(oola.request_date))
    --,ooha.order_number SO#
    -- ,oola.line_number Line#
    ,msib.segment1||'.'||msib.segment3 Item#
    ,nvl(hcasa.translated_customer_name, hp.party_name) Customer_Name
    --,trunc(oola.request_date)
    from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,mtl_system_items_b msib
    ,oe_transaction_types_tl ot
    ,so_lookups sscv
    ,hz_cust_site_uses_all hcsua
    ,hz_cust_acct_sites_all hcasa
    ,hz_parties hp
    ,hz_cust_accounts hca
    ,mtl_item_categories mic
    ,mtl_categories mc
    ,mtl_category_sets mcs
    ,mtl_parameters oog
    ,hz_party_sites hps
    ,oe_hold_sources_all ohs
    ,oe_order_holds_all oohlda
    ,wsh_delivery_details wdd
    ,ccw_alloc_lookup cal
    ,fnd_lookup_values fnd
    where ooha.header_id = oola.header_id
    and ooha.order_type_id = ot.transaction_type_id
    and ot.language='US'
    and sscv.lookup_code = ooha.sales_channel_code
    and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    and ooha.ship_to_org_id = hcsua.site_use_id(+)
    and hcsua.cust_acct_site_id= hcasa.cust_acct_site_id(+)
    and ooha.sold_to_org_id = hca.cust_account_id(+)
    and hca.party_id = hp.party_id(+)
    and hcasa.party_site_id = hps.party_site_id(+)
    and oola.inventory_item_id = msib.inventory_item_id
    and mc.category_id = mic.category_id
    and mcs.category_set_id = mic.category_set_id
    and mic.inventory_item_id = msib.inventory_item_id
    and mic.organization_id = msib.organization_id
    and oola.flow_status_code in ('AWAITING_SHIPPING', 'BOOKED', 'PRODUCTION_COMPLETE', 'PRODUCTION_OPEN')
    and mcs.category_set_name = 'Inventory'
    and oola.ship_from_org_id = msib.organization_id
    and oog.organization_id = msib.organization_id
    and oola.inventory_item_id = ohs.hold_entity_id
    and ohs.hold_id = 1026
    and ohs.hold_entity_code = 'I'
    and ohs.released_flag != 'Y'
    and ohs.hold_release_id is null
    -- and oohlda.hold_source_id = ohs.hold_source_id
    and oohlda.hold_release_id is null
    and oohlda.header_id = oola.header_id
    and oohlda.line_id = oola.line_id
    and wdd.released_status != 'Y'
    and wdd.source_header_id = oola.header_id
    and wdd.source_line_id = oola.line_id
    and wdd.date_scheduled is null
    and cal.location_id(+) = hps.party_site_number
    and upper(sscv.meaning) = fnd.lookup_code(+)
    and fnd.lookup_type(+) = 'CCW_ALL_SALES_CHANNEL_CODE'
    and sscv.lookup_type='SALES_CHANNEL'
    and rownum <2
    and NOT EXISTS (SELECT 'X'
    FROM CCW_ALLOC_DSOH DSOH2
    where dsoh2.dmdunit = msib.segment3
    and dsoh2.loc = HPS.PARTY_SITE_NUMBER)
    and msib.segment3 = '63535'
    group by msib.segment1||'.'||msib.segment3
    ,nvl(hcasa.translated_customer_name, hp.party_name)
    -- ,ooha.order_number
    -- ,oola.line_number




    Now, I just wanted only one record from all the required field. I mean in above query i have commented SO# AND LINE# Because when i group by them with other field it gives me unwanted records. i mentioned that even though there are more SO# and line# i just want first one.

    Thanks in Advance

    Vipul

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What do you mean by the "first" one? Do you mean the SO# and LINE# corresponding to the min(trunc(oola.request_date)) value?

    The way to do that is to divide the problem into 2 parts:
    1) Find the min(trunc(oola.request_date)) values for each customer (or whatever) - something like this:

    Code:
    select customer, min(trunc(oola.request_date))
    from ...
    group by customer;
    2) Select the record(s) for each customer that match those dates:
    Code:
    select ...
    from ...
    where (customer, trunc(oola.request_date)) in
    ( select customer, min(trunc(oola.request_date))
      from ...
      group by customer
    );
    Here is an example using the standard EMP table:
    1) Minimum hire date by deptno:
    Code:
    SQL> select deptno, min(trunc(hiredate))
      2  from   emp
      3  group by deptno;
    
        DEPTNO MIN(TRUNC(H
    ---------- -----------
            10 09-JUN-1981
            20 17-DEC-1980
            30 20-FEB-1981
    2) Details of the employees who are earliest hire in each deptno:
    Code:
    SQL> select emp.*
      2  from   emp
      3  where  (deptno, trunc(hiredate)) in
      4  ( select deptno, min(trunc(hiredate))
      5    from   emp
      6    group by deptno
      7  );
    
         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
          7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
    You will need to adapt this approach to your specific query, which is too large and complex for me to understand!

Posting Permissions

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