Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2002
    Location
    Dunedin NZ
    Posts
    7

    Unanswered: Using calculated fields in select queries

    Hi

    This has to be easy (I hope).
    In Access (and Oracle I think) you can write somthing like:

    Select a + b * as aField,
    c * d as anotherField,
    aField * anotherField as result
    From someTable
    where whateverSuits

    However good old SQL Server 7 says

    'Invalid column name...'

    How do I write in T_SQL

    Any help would be very much appreciated.



  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    What column name was invalid? Because that should work, however it would error out on a + b * as aField, because of the * before AS.
    MCDBA

  3. #3
    Join Date
    Jul 2002
    Location
    Dunedin NZ
    Posts
    7
    Originally posted by achorozy
    What column name was invalid? Because that should work, however it would error out on a + b * as aField, because of the * before AS.
    Thanks for your reply

    Yeh I thought it should work also but it doesn't

    Sorry about the * in 'a + b * as aField'
    it should read a + b as aField

    The error message is:
    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'aField'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'anotherField'.

    Griff

  4. #4
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    Try to use this :

    Select *,
    a + b as 'Field',
    c * d as 'anotherField',
    (a + b) * (c * d) as 'result'
    From test2

    that works.

    ===========================================

    because this:
    Select *,
    a + b as 'Field',
    c * d as 'anotherField',
    Field * anotherField as 'result'
    From test2
    won`t work./
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  5. #5
    Join Date
    Jul 2002
    Location
    Dunedin NZ
    Posts
    7
    Thanks Diogo

    Problem with your solution is it fine in a simple case. What I really want to do is quite a bit more involved than a simple a * b and worse I need to know the variance between to totals so the final calculations looks like
    bigUglyCalculation / someOtherCalculation * anotherCalulation bigUglyCalculation.

    So is this just not possible in T-SQL (Im beginning to think not) and if not a question for Bill (Gates that is) WHY.?


    Griff

  6. #6
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    This may be entirely too simple,, but what if a go statement were inserted between the calculations.

    a + b as field
    c + d as anotherfield
    go
    field * anotherfield as result.

    this should allow tsql to 'know' what a + b is before it is used.

    Otherwise declare variables, set them (set @field as a + b)
    then
    select @field + @anotherfield as result.

    Let me know if this helps.

    Brent

  7. #7
    Join Date
    Jul 2002
    Location
    Dunedin NZ
    Posts
    7
    Thanks for your in put Brent

    Not quite sure what you mean by using go or I havn't made my requirements clear.

    The select query in question will return a number of records and I wanted the 'result' available immediately instead of having to then traverse a cursor, as I would in your second idea.

    So why not use your second idea. Well nothing really, it will slow the query down a bit but that not my concern.
    I guess its a bit of a bury your head in the sand thing. I could do it in Oracle and I could do it in Access so why not in T-SQL?

    But as it appears you cant then Im left with little other choice than to go to option two. Its probably more readable anyway.

    Jamie

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I think you need to put brackets around you calculations, here is a small example

    Table Test
    Code:
    select * from test
    
    colA        colB        colC            
    ----------- ----------- --------------- 
    100         80          40.00
    100         100         25.00
    Example calculation
    Code:
    select	((colA + colB) - colC) 	AS x,
    	(colA * colB * colC)	AS y,
    	((colA / colB) * colC)	AS z
    from  test
    
    x                y                          z                          
    ---------------- -------------------------- -------------------------- 
    140.00           320000.00                  40.00
    175.00           250000.00                  25.00
    MCDBA

  9. #9
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Wink

    Hy guys,

    I think we aren`t understanding Griff very well.!

    Griff,
    You want to give names to calculated Columns and can use them in the same query. Right?

    He wantsomething like this:

    SELECT (a + b +c +d +z) as Expr1,
    ((z *x) + (a+g+b ) as Expr2.
    Expr1 + Expr2 -- HERE IS THE PROBLEM
    FROM table

    he won`t get this.
    This would generate a error message, because the table don`t have Expr1 and(or) Expr2.


    Bye
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  10. #10
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If he wants this

    SELECT (a + b +c +d +z) as Expr1,
    ((z *x) + (a+g+b )) as Expr2.
    Expr1 + Expr2 -- HERE IS THE PROBLEM
    FROM table

    Then code this

    SELECT (a + b +c +d +z) as Expr1,
    ((z *x) + (a+g+b )) as Expr2.
    (a + b +c +d +z) + ((z *x) + (a+g+b)) as Expr3
    FROM table
    MCDBA

  11. #11
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    I think this is more what your looking at. I created the table numbers and populated a few columns of data and then ran this code against it.

    use testing
    select r.expr1 * r.expr2 as result
    from (select cola + colb as expr1, colc + cold as expr2 from numbers) as r
    go

    The output of this code is a single column called result.

    Brent

  12. #12
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Why???

    use testing
    select r.expr1 * r.expr2 as result
    from (select cola + colb as expr1, colc + cold as expr2 from numbers) as r
    go

    Why do more work then needed.

    use testing
    select (cola + colb) * (colc + cold) as result from numbers
    go

    Original Post
    Select a + b as aField,
    c * d as anotherField,
    aField * anotherField as result
    From someTable
    where whateverSuits

    Answer
    Select a + b as aField,
    c * d as anotherField,
    (a + b) * (c * d) as result
    From someTable
    where whateverSuits
    MCDBA

  13. #13
    Join Date
    Jul 2002
    Location
    Dunedin NZ
    Posts
    7
    Thanks Brent for your answer and everyone else who replied (great)

    That works!!!
    Funny its not as straight forward as I expected.

    In answer to achorozy question "WHY"

    The final calculation I want to do is something like

    bigUglyCalculation / someCalculation * anotherCalulation - bigUglyCalculation

    and it seem simply wrong to repeat the calculation twice in the same line practically as it is a big ugly thing in the first place.
    I would also expect it would ultimately me more work to repeat the calculation twice.

    My apologies to all that I confused with the simple example I gave

    Griff

  14. #14
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    heheh!
    This Question really generated a great discussion.

    This is Very good to all us!

    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •