Referencing expressions in other expressions in a single select statement 
Author Message
 Referencing expressions in other expressions in a single select statement


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

Fri, 28 Jul 2000 03:00:00 GMT
 [ 1 post ] 

 Relevant Pages 

1. Regular Expressions in SELECT statement?

2. Conditional expressions within a select statement

3. UDF as expression in select statement

4. Regular expressions in SELECT statement?

5. ORA-01790: expression must have same datatype as corresponding expression

6. Expressions in ADO recordset field reference

7. Name expressions (indirect reference) and LOOKUP()

8. DBase/SunOS: referencing tag in SET RELATION TO expression

9. Using DateDiff expression in an SQL statement

10. if statement within if boolean expression

11. CASE statement on expression?

12. case statement returns multiple expressions?

Powered by phpBB® Forum Software