How to refer to subselect columns in select statement

Hi everybody,

I have been looking around for an answer to this one, but haven't had any luck so far. Hope some of you clever minds out there can help me out on this :-)

I have an sql statement that does updates into another table like this:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2'

select 0 as 'zero'

from my_table where ("some clause")

Now, I want to replace the 'zero' column with something useful like the sum of subselect 1 and 2. How can I do that? The only solution I can make work is something like:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2',

(

(select number1 from some_other_table where (... linked)) +

(select number2 from some_third_table where (... again linked))

) as 'sub1_plus_sub2'

from my_table where ("some clause")

It works, but in my current project I would end up with tons of duplicate lines. This way I would end up having to maintain select code several places.

Is there a way to refer to a column in the same select statement that you're executing? In the ORDER BY clause you can use things like ORDER BY 1,2 to refer to columns without having to use the entire statement again, but I haven't been able to find anything that seems useful on this subject. I guess what I miss is a way like

field(2).value + field(3).value

Best regards, Jan

In T-SQL you can't do as you guess..

But it is possible to reuse the values. For achive your requirement you have to use the derived table..

Code Snippet

insert into result_table

Select

[Value 1]

,[My Sub1]

,[My Sub2]

,[My Sub1] + [My Sub1] as [Sub1_Pluse_Sub2]

From

(

select

value1 as [Value 1]

,(select number1 from some_other_table where (... linked)) as [My Sub1]

,(select number2 from some_third_table where (... again linked)) as [My Sub2]

from

my_table

where

("some clause")

) as Data

|||

try this instead

INSERT
INTO result_table
SELECT value1 as 'value 1'
, some_other_table.number1 AS 'my sub1'
, some_third_table.number2 AS 'my sub2',
, some_other_table.number1 + some_third_table.number2 as 'sub1_plus_sub2'
FROM my_table INNER JOIN
some_other_table ON (.. linked) INNER JOIN
some_third_table ON (... again linked)
WHERE ("some clause")