Query takes a long time to run when using an NVL statement for a parameter and passing no value for
Hello All,
I am running a query with an "AND" filter and mtl.inventory_item_id = nvl('&4',mtl.inventory_item_id), when I don't pass a value in for parameter 4 the query takes an hour to completely run. When I remove or pass a value the query takes less than 30 seconds. Any idea's why?
Database version is 11.2.0.1.
Full querry
SELECT hdr.trx_date gl_date,
nlas_get_cust_num(sold_to_customer_id) customer_num, --to_char(ob.customer_number,'000009') customer_num,
to_char(nlas_invoice_to_Suffix(hdr.trx_number,hdr.cust_trx_type_id),'009') suffix, --to_char(ob.sales_document_name,'009') suffix,
I am running a query with an "AND" filter and mtl.inventory_item_id = nvl('&4',mtl.inventory_item_id), when I don't pass a value in for parameter 4 the query takes an hour to completely run. When I remove or pass a value the query takes less than 30 seconds. Any idea's why?
Database version is 11.2.0.1.
Full querry
SELECT hdr.trx_date gl_date,
nlas_get_cust_num(sold_to_customer_id) customer_num, --to_char(ob.customer_number,'000009') customer_num,
to_char(nlas_invoice_to_Suffix(hdr.trx_number,hdr.cust_trx_type_id),'009') suffix, --to_char(ob.sales_document_name,'009') suffix,
0