I am using two functions in a Select statement. Let's say A and B are functions which contain large code, and both...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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 about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue 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.