Referencing expressions in other expressions in a single select statement

Hello-

I am using SQL-Server 6.5, and I need help with the following:

For the purposes of reporting (i.e. I do not need to update any of the

underlying data going into my queries) I want to be able to reference an

expression in the formula for another expression within a single select

statement. The following bone-simple version should explain this:

SELECT a + b AS c, d + e AS f, c * f AS g

FROM tablename

You can't do this in MS-SQL, but you can do it in Access, where I have

been prototyping.

Obviously in this case I could easily substitute the formulas for c and

f, resulting in:

SELECT a + b AS c, d + e AS f, (a + b) * (d + e) as g

FROM tablename

However, suppose that the formulas for c and f were very complex

expressions, each requiring the use of the products of multiple,

embedded CASE statements. Not so easy any more to debug, or to

propogate changes to the formulas for c or f.

The various solutions I have thought through are:

1- create a series of VIEWs. However, because of my need to

distribute to clients, this is a

highly non-optimal solution.

2- use a series of cursors. My understanding of cursors, however

slim, suggests that this will

necessarily mean locking all underlying tables until the final

resulting recordset is closed.

If true, this is not acceptable either. Still, my understanding

of cursors is very slim and I

may be entirely in the wrong here.

3- Create a dynamic SQL model where I store the formulas for the

bottom-level expressions

in variables, and create the mid-level expressions by

concatenating strings utilizing the

bottom-level expressions, and cascade the whole process. This

is not really problematic,

but lacks the intuitive appeal of being able to reference other

expressions in the query.

If anyone has any insight into this, please e-mail me and respond to the

group. I appreciate your help.

Thanks very much,

John Mulliken