sql - Receiving ORA-01427 in specific date ranges

Question

I have an ORACLE BIP query returning ORA-01427 within various date ranges. I'm not understanding the error and believe I have it narrowed own to a specific line. I believe my subquery..... (select Distinct DISPLAYED_FIELD FROM AP_LOOKUP_CODES where lookup_type = 'AP_ACCOUNTING_STATUS' and lookup_code = AP_INVOICES_UTILITY_PKG.GET_ACCOUNTING_STATUS(a.INVOICE_ID)FETCH FIRST 1 ROWS ONLY) AS ACCOUNTING_STATUS,...is throwing the error. I have tried various fixes but nothing seems to resolve the issues. Including utilizing an In condition or ROWNUM but have had no success. My goal would be to get any date range to return without an error. I know the space I'm working in was cloned from another environment recently

Select a.invoice_id, a.invoice_num,

to_char(a.invoice_date,'MM/DD/YYYY'),

to_char(a.terms_date,'MM/DD/YYYY'), a.invoice_currency_code, a.invoice_amount, n.vendor_name, s.vendor_site_code ,c.title, c.description, prf.name as Entity, bus.Bu_Name,

to_char(d.creation_date,'MM/DD/YYYY'), d.Category_Name,

Case When to_number(c.dm_version_number) is null  Then  p.did  Else to_number(c.dm_version_number)  End       

document_id,

doc.datatype_code,

(select distinct to_char(b.accounting_date,'MM/DD/YYYY') from ap_invoice_distributions_all b where a.invoice_id = b.invoice_id ) as accounting_date,

(select Distinct DISPLAYED_FIELD FROM AP_LOOKUP_CODES where lookup_type = 'AP_ACCOUNTING_STATUS' and lookup_code = AP_INVOICES_UTILITY_PKG.GET_ACCOUNTING_STATUS(a.INVOICE_ID)FETCH FIRST 1 ROWS ONLY) AS ACCOUNTING_STATUS,

(SELECT DISPLAYED_FIELD FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'INVOICE PAYMENT STATUS'

AND LOOKUP_CODE = a.PAYMENT_STATUS_FLAG) AS PAYMENT_STATUS

 

from ap_invoices_all a,poz_supplier_sites_v s, poz_suppliers_v n, fnd_attached_documents d ,fnd_documents_tl c , revisions p, xle_entity_profiles prf,  fun_all_business_units_v bus, fnd_documents doc

where a.vendor_id = s.vendor_id

and a.vendor_site_id = s.vendor_site_id

and a.party_site_id = s.party_site_id

and a.vendor_id = n.vendor_id

and a.party_id = n.party_id

and d.pk1_value = a.invoice_id

and d.entity_name = 'AP_INVOICES_ALL'

and d.document_id = c.document_id

and c.document_id = doc.document_id

and a.legal_entity_id = prf.legal_entity_id

and a.legal_entity_id = bus.legal_entity_id

and substr(a.image_document_num,3) = p.ddocname

and (n.vendor_name in (:Supplier) or 'All' in (:Supplier || 'All'))

and (prf.name  in (:Legal_Entity) or 'All' in (:Legal_Entity || 'All'))

and (a.invoice_currency_code in (:Currency) or 'All' in (:Currency ||'All'))

and (bus.bu_name in (:Business_Unit) or 'All' in (:Business_Unit ||'All'))

and ((  a.invoice_num = :Invoice_Number  AND :Invoice_Number is not null)

or (1 = 1 and  :Invoice_Number is null) )

And ((CASE WHEN :Date_Type = 'Invoice Date' Then a.invoice_date WHEN :Date_Type = 'Accounting Date' Then a.gl_date WHEN :Date_Type = 'Invoice Received Date'  Then a.terms_date  END) between :Start_Date and :End_Date)

 

And (((Case when :Amount_Range = 1 Then a.invoice_amount END) between 0 and 5000 )

or ((Case when :Amount_Range = 2 Then a.invoice_amount END) between 5001 and 50000 )

or  ((Case when :Amount_Range = 3 Then a.invoice_amount END) between 50001 and 999999 )

or ((Case when :Amount_Range = 4 Then a.invoice_amount END) > 1000000 )

or (1=1 and :Amount_Range = 0))

order by a.invoice_id

question from:https://stackoverflow.com/questions/65904150/receiving-ora-01427-in-specific-date-ranges

Answer 1

You just need any record from given query then use max without group by as follows:

(select max(DISPLAYED_FIELD)
  FROM AP_LOOKUP_CODES 
 where lookup_type =  'AP_ACCOUNTING_STATUS' 
   and lookup_code = AP_INVOICES_UTILITY_PKG.GET_ACCOUNTING_STATUS(a.INVOICE_ID)
) AS ACCOUNTING_STATUS