June 28, 2006 at 1:51 pm
Howdy.
This may be a simple question, but I can't figure a way to describe it in Googlish terms.
select 1 as first, 2 as second, first + second
Basically I want to reference a column of a query by the name I've given it. In my actual case I have a column that is a complicated expression, and I'd like to use that same complicated expression as part of another column's expression. But referencing it by the name I've given it doesn't work.
Suggestions?
Thanks
June 28, 2006 at 1:55 pm
Select out of a derived table:
Select first, second, first + second
From
-- Create derived table within parentheses
(
Select 1 As first, 2 As second
From Yourtable
) dt
June 28, 2006 at 2:07 pm
I see how that would work for my example, but I can't wrap my head around how I would make my real query work like that. Is that the only way to reference a column from within the same query (a column that's really an expression)?
Real query below. The two big columns, [Finding Cost $/BOE] and [Dev Cost] would really benefit from being able to reference other columns that are expressions. Otherwise I see myself essentially expanding those with the actual expressions, and that would be unmaintainable.
SELECT
state,
field,
acq.name as Deal,
e.name as [Well Name],
well_no,
afe_no,
zone,
post_review,
total_cost * total_wi as [Net Actual Cost],
gross_oil_res_added_post * total_nri as [Net Oil Reserves],
gross_gas_res_added_post * total_nri as [Net Gas Reserves],
gross_oil_res_added_post * total_nri + gross_gas_res_added_post * total_nri / 5.5 as [Net BOE],
case
when gross_oil_res_added_post * total_nri + gross_gas_res_added_post * total_nri / 5.5 > 0
then
(total_cost * total_wi
/ (gross_oil_res_added_post * total_nri + gross_gas_res_added_post * total_nri / 5.5))
/ 1000
else 0
end as [Finding Cost $/BOE],
case
when [Finding Cost $/BOE] > 0
then
[Net Actual Cost]
/ ([Post Inc Rates Oil] + ([Post Inc Rates Gas] / 5.5) * total_nri)
else 0
end as [Dev Cost],
incremental_rates_oil_post * total_nri as [Net Oil Prod],
incremental_rates_gas_post * total_nri as [Net Gas Prod],
incremental_rates_oil_post * total_nri + incremental_rates_gas_post * total_nri / 5.5 as [Net Prod BOE]
from
dbo.afe a
LEFT JOIN #tmp_cum tmpc on a.id = tmpc.afe_id
LEFT JOIN #tmp_int tmpi on a.id = tmpi.afe_id
LEFT JOIN dbo.entity e on a.id_entity = e.id
LEFT JOIN dbo.acquisition acq on acq.id = e.id_acquisition
LEFT JOIN dbo.engineering eng on eng.id_afe = a.id
LEFT JOIN dbo.land l on l.id_afe = a.id
June 29, 2006 at 6:52 am
Yes, there are only two ways to handle this situation. Either you have to repeat the entire calculation of [Finding Cost $/BOE] column, or you have to use derived table. You can not reference a column that is created within the same SELECT and use the alias of that column.
First I would suggest using table names/aliases with all columns (e.g. a.incremental_rates_oil_post * tmpc.total_nri as [Net Oil Prod]); it helps a lot when you try to resolve problems... and then decide which of the two solutions is better for you. I prefer derived tables.
I also use to write commands and keywords with capital letters to make the code more readable.
June 29, 2006 at 7:28 am
Since your issue is maintenance/code reuse, a view would be the best solution, rather than a derived table (inline view).
It's not particularly useful here since the calculations are simple and the columns involved numerous, but you could also use a scalar UDF if you need to reuse the same calculation in a number of different queries.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply