Home » SQL & PL/SQL » SQL & PL/SQL » Calculate with calculated fields
Calculate with calculated fields Wed, 24 February 2010 08:00
 steffeli Messages: 112Registered: 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
 joy_division Messages: 4759Registered: 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
 cookiemonster Messages: 12803Registered: 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
 steffeli Messages: 112Registered: 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
 joy_division Messages: 4759Registered: 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
 steffeli Messages: 112Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 BlackSwan Messages: 25543Registered: 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
 JRowbottom Messages: 5933Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 JRowbottom Messages: 5933Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 JRowbottom Messages: 5933Registered: 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: Thu Jul 27 00:17:28 CDT 2017

Total time taken to generate the page: 0.13996 seconds