I am using two functions in a Select statement. Let's say A and B are functions which contain large code, and both functions return a numeric data type only. In the Select statement, I want to subtract those two functions like this --
SELECT A(), B(), A()-B() FROM mytable
So I need to call functions A and B twice in a single statement. Because these two functions have a lot of code, performance is decreasing. Is there any alternate way?
Yes. Write a SELECT query to calculate the functions once only, and then query that query.
select result_of_A , result_of_B , result_of_A - result_of_B from ( select A() as result_of_A , B() as result_of_B from mytable ) as T
Here the subquery is used in the FROM clause as a derived table. Every query produces a result set, which is actually a table, and can be used as such anywhere in SQL where you are allowed to use a table. This property is called orthogonality, or something, and it's a feature of the relational model, I think. If I sound unsure, it's because I'm no expert on the relational model or set theory, I just know that you can use a query wherever you can use a table.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.