select count(OBJID) as counter  from oaminspectorshipments where ridkey = 'psi08' 
AND OBJID IN ( select PRESENTATION_ID from 
INSPECTOR_SHIPMENTS_PROPOSALS where 
PROPOSAL_ID = (  select agreement_id from oamInspectorPOs where 
lower(PURCHASEORDER) = lower('tony-filter03') ) ) 

SELECT A.* FROM (select RIDKEY,OBJID,shipment_number,NAME as 
seller,amount as invoiceAmount,currency as 
invoiceCurrency,status,insepction_result,lastdate,
presentation_Date,operationCode, PACKING_LIST_DOCID, 
COMMERCIAL_INVOICE_DOCID,version,last_msg_id, unread, 
INSPECTION_REPORT_DOCID, CREATIONDATE , 
row_number() over ( ORDER BY lastdate DESC) as rn 
FROM oaminspectorshipments where ridkey='psi08' AND 
OBJID IN ( select PRESENTATION_ID from 
INSPECTOR_SHIPMENTS_PROPOSALS where 
PROPOSAL_ID = (  select agreement_id from 
                oamInspectorPOs where lower(PURCHASEORDER) = 
                lower('tony-filter03') ) )) A    
WHERE rn BETWEEN 1 and 0 

select a.*
from   (select ridkey
               , objid
               , shipment_number
               , name as seller
               , amount as invoiceamount
               , currency as invoicecurrency
               , status
               , insepction_result
               , lastdate
               , presentation_date
               , operationcode
               , packing_list_docid
               , commercial_invoice_docid
               , version
               , last_msg_id
               , unread
               , inspection_report_docid
               , creationdate
               , row_number() over(order by lastdate desc) as rn
        from   oaminspectorshipments
        where  ridkey = 'psi08'
        and objid in (select presentation_id
                      from   inspector_shipments_proposals
                      where  proposal_id = (select agreement_id
                                     from   oaminspectorpos
                                     where  lower(purchaseorder) = lower('tony-filter03')))) a
where  rn between 1
           and 0

  1  select *
  2  from   (select row_number() over (order by empno) as rn
  3          from emp
  4         )
  5* where  rn between 1 and 0
SQL> /

no rows selected