Q
Manage Learn to apply best practices and optimize your operations.

How to query a query

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.

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.

This was last published in September 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close