Home » SQL & PL/SQL » SQL & PL/SQL » Calculate with calculated fields
Calculate with calculated fields [message #444838] Wed, 24 February 2010 08:00 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi, can I use calculated fields (b) for another calculation (c) in the same select?

select 10*a as b,
       10*b as c
from tab


The code gives an error, but how can I do this?

Thanks, Stefan
Re: Calculate with calculated fields [message #444841 is a reply to message #444838] Wed, 24 February 2010 08:01 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I don't see any error.
Re: Calculate with calculated fields [message #444843 is a reply to message #444838] Wed, 24 February 2010 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use an inline view:
SQL> WITH tab AS (SELECT 2 a FROM dual)
  2  select b, 10*b AS c FROM (SELECT 10*a as b
  3                            from tab);

         B          C
---------- ----------
        20        200

SQL> 
Re: Calculate with calculated fields [message #444845 is a reply to message #444843] Wed, 24 February 2010 08:16 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Thanks, but is there no way to do this in the same select?

Re: Calculate with calculated fields [message #444847 is a reply to message #444845] Wed, 24 February 2010 08:19 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
select 10*a as b, 10*(10*a) as c from tab;


Parenthesis for aesthetic purposes only.
Re: Calculate with calculated fields [message #444848 is a reply to message #444847] Wed, 24 February 2010 08:47 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
unfortunately, the calculated field (b) is complicate to calculate (several rows of code), to refer to the calculated values would be great. No solution for this in the same select?
Re: Calculate with calculated fields [message #444849 is a reply to message #444848] Wed, 24 February 2010 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Why inline view does not fit your need?

Regards
Michel
Re: Calculate with calculated fields [message #444851 is a reply to message #444848] Wed, 24 February 2010 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Calculate with calculated fields [message #444858 is a reply to message #444838] Wed, 24 February 2010 09:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Simply put - no.

There is no way of aliasing a column so that that alias can be referred to in other columns of the select list.

One additional option would be to create a pl/sql function that returns the calculated value - then you can simply refer to this function in multiple places in the SQL
Re: Calculate with calculated fields [message #444860 is a reply to message #444858] Wed, 24 February 2010 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
One additional option would be to create a pl/sql function that returns the calculated value - then you can simply refer to this function in multiple places in the SQL

Aaaarrgh! And kill the optimizer and performances.

Regards
Michel
Re: Calculate with calculated fields [message #444862 is a reply to message #444860] Wed, 24 February 2010 10:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the function doesn't do any SQL then the performance hit shouldn't be too bad.

Plus, when you get to 11g it could benefit from the results cache
Re: Calculate with calculated fields [message #444871 is a reply to message #444862] Wed, 24 February 2010 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is not the case here, the select field is 10*<expression computed from a table column>.

2/ Agree but only if the function is deterministic

Regards
Michel
Re: Calculate with calculated fields [message #444876 is a reply to message #444871] Wed, 24 February 2010 10:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well,
a) This is a simplified example,

b) If you create the function as
CREATE OR REPLACE FUNCTION fn_1 (p_in  number) return number as
BEGIN
  return 10*p_in;
END;

then the query would become
select fn_1(a) as b,
       fn_1(fn_1(a)) as c
from tab
- no SQL involved.

However, the actual situation depends completely on information that the OP seems unwilling to share.
Previous Topic: create new column
Next Topic: Duplicate Detection
Goto Forum:
  


Current Time: Fri Dec 09 15:08:03 CST 2016

Total time taken to generate the page: 0.20745 seconds