Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unanswered: Running the same subquery multiple times

    Hi,

    I was wondering if this can be done...

    I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

    E.g.
    I have a table with 2 cols - amount, date.

    SELECT total_amount, closing_amount,
    FROM table1
    GROUP BY month(date)

    Total amount is the SUM(amount) for the month.
    Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

    As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

    This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

    Hope someone can help,
    Thanks,
    Stuart

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a VIEW

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    25
    --Run this in a query analyzer or sp
    Declare @Total_Amount numeric (9)
    Declare @closing_amount numeric (9)

    Set @Total_Amount = (SELECT sum(total_amount) FROM table1 GROUP BY month(date))

    --Make sure you only return 1 record here
    Set @closing_Amount = (SELECT closing_amount FROM table1 Where Column_Value = my_Value GROUP BY month(date))

    print @Total_Amount
    print @closing_Amount

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    db0, i don't believe you can assign a column of values to a variable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    10
    What I did eventually was use a whole load of user-defined functions. Some of them get called multiple times. but it seems quick enough.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and the reason you chose not to use a VIEW was... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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