The software I work on allows users to enter multiple criteria when running reports (ex: Order, Date, Customer...). When the user runs the report the selected criteria is saved in a global variable of type VARCHAR2 as a csv string. So if the user only wanted to see orders 100,101,102, this would be saved as "100,101,102" in the gOrdersList variable. As a result we have to limit the report query by this global variable, which looks like below.
SELECT *
FROM ORDERTABLE
WHERE 1 = DECODE(gOrdersList, NULL, 1, CUST.ISIN(ORDERTABLE.ORDDERNUM, gOrdersList))
FYI- CUST.ISIN() : This function validates if a string is in a CSV variable. 1 = Found, 0 = Not found.
As you can image when the query is large and the user enters several different criteria, the report speed is negatively impacted.
I am trying to figure out the best way to redesign the reports. I can't change the way we store the criteria, so we are stuck with the CSV variable to limit the report.
Question 1: Is there a more effeciant way to limit a query by a csv string?
Question2: I am thinking the best approach might be to take the CSV global variables, load them into temp tables then join to the tables in my query. This way I wouldn't be calling a function in the Where clause for every row returned.
Open to suggestions and thanks in advance.