1 Reply Latest reply on Mar 5, 2015 11:45 AM by rioman

    Using ROWID returns less rows than if use distinct in query

    tafaj_42

      Hi,

       

      I am writting  the following query to get distinct purchase order number from a specific received date. this returns me total 620 rows.:

       

      select  distinct pha.segment1 from apps.po_headers_all pha, apps.RCV_TRANSACTIONS rcv

      where 1=1

      and trunc(rcv.transaction_date) between to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS') and to_date(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')

      and pha.po_header_id=rcv.po_header_id

      and pha.type_lookup_code='STANDARD'

      and pha.authorization_status ='APPROVED'

      and pha.cancel_flag='N'

      ;

       

      if i write the query using ROWID to get the distinct Purchase order number its giving me 580 rows:

       

      select  pha.segment1 from apps.po_headers_all pha, apps.RCV_TRANSACTIONS rcv

      where 1=1

      and trunc(rcv.transaction_date) between to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS') and to_date(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')

      and pha.po_header_id=rcv.po_header_id

      and rcv.rowid=(select max(rowid) from apps.RCV_TRANSACTIONS rcv where rcv.po_header_id=pha.po_header_id)

      and pha.type_lookup_code='STANDARD'

      and pha.authorization_status ='APPROVED'

      and pha.cancel_flag='N';

       

      Can anyone tell me why? I want to use this ROWID query in a valueset in oracle apps.