Hi All,
I have a situation, where I have to use SQL Statements directly (meaning SQL Statements not wrapped in procedure/functions). Because the tool, on which we are working on, is built to only support direct SQL and pass it to the database and fetch the results back. So, in order to achieve some requirements, I had to resort to SQL with the use of various GUI parameters, in such a way that the SQL returns only the result set based on the parameters selected in the GUI.
So, my questions on this are:
1. Does SQL support short-circuit evaluation? In this regard, I have put parameter selected in the GUI as the first condition and rest of the SQL statement part of the other AND condition. So, will the SQL part of the condition will be evaluated regardless even when the parameter is false? (eg: <test parameter> AND <SQL Statement>) . If so, is there any way to ensure the SQL statement gets evaluated ONLY after the parameter condition is satisfied?
2. Will the Custom SQL be preserved in the shared memory pool, similar to the stored procedures, so we can avoid re-compilation, and re-evaluation of costs?
3.I have a bunch of Union Clauses and these return results with data ONLY after a particular parameter condition satisfies. So, in order to make this work, I have a UNION statement and put the parameter condition in the WHERE clause, but in this case, I believe all the JOINS with the tables in the SQL statement are evaluated first and then comes in the WHERE condition to check for the parameter (eg: SELECT * FROM TABLE A INNER JOIN TABLE B ON A.ID = B.ID WHERE <parameter1> = TRUE). If this is how it works, then by this way, we are wasting resources. So, in this regard, do you have any other alternative, which immediately skips the Union Condition altogether when a parameter condition is false, instead of going through the joins and evaluating the condition in the WHERE clause.
Not sure, if I have conveyed the questions clearly. Please let me know, if you need any more information, or something is not clear.
Please note, only SQL statements are to be used and not procedures.
Thanks for your help!