Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Unanswered: Need to get column and assign value in one select

    Hi. I get an error saying that 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations' when I try to select a column (that i need for a group by) that's in the same select where a value is assigned to a variable - here it is:

    declare @acty FLOAT

    SELECT @acty = eval_id,
    sum(cast(ques.answer_value AS INTEGER))
    FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
    WHERE ques.score_value_ind = 'S'
    and ques.tx_id = #ytd_eval1a.tx_id
    and ques.code = ans.code
    and ques.answer_value = ans.answer_value
    and ans.valid_answer <> 'NA'
    group by eval_id

    I need the group by eval_id so that the SUM above is broken out by eval_id.

    How else can I get the value of @acty broken out by eval_id? Any help is greatly appreciated

  2. #2
    Join Date
    Jun 2003
    Posts
    5

    Re: Need to get column and assign value in one select

    If I understand what you are doing, you are trying to get the sum of the answer values for each eval_id. Your error message is telling you that you can not select @variable = value from table at the same time as you select count(value) from table. If you are only going to have 1 eval_id you could select @acty = eval_id, @sum = xxxx from xxxx. Another aproach might be: remove your variable assignment and select the result set into another table (probably a temp) and then select the sum(values) out of that table based on the eval_id. That way you get the whole result set out of your query and then parse through it assigning each value to a variable.


    Originally posted by Coder7
    Hi. I get an error saying that 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations' when I try to select a column (that i need for a group by) that's in the same select where a value is assigned to a variable - here it is:

    declare @acty FLOAT

    SELECT @acty = eval_id,
    sum(cast(ques.answer_value AS INTEGER))
    FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
    WHERE ques.score_value_ind = 'S'
    and ques.tx_id = #ytd_eval1a.tx_id
    and ques.code = ans.code
    and ques.answer_value = ans.answer_value
    and ans.valid_answer <> 'NA'
    group by eval_id

    I need the group by eval_id so that the SUM above is broken out by eval_id.

    How else can I get the value of @acty broken out by eval_id? Any help is greatly appreciated

  3. #3
    Join Date
    Mar 2003
    Posts
    6
    Thanks very much for your suggestion.

    I figured that one out as:

    SELECT sum(cast(ques.answer_value AS INTEGER)) AS actytd, eval_id
    FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
    WHERE ques.score_value_ind = 'S'
    and ques.tx_id = #ytd_eval1a.tx_id
    and ques.code = ans.code
    and ques.answer_value = ans.answer_value
    and ans.valid_answer <> 'NA'
    GROUP BY eval_id
    I was able to eliminate the value assignment.

    But now I'm stuck on the next query that has to be broken out by eval_id because it has subselects. I am already using 2 temp tables so I'm hesitating to use any more.

    What's your take on grouping the following by eval_id?

    SELECT sum(q.maxValues)
    FROM
    (
    SELECT DISTINCT code, MAX(answer_value) AS maxValues
    FROM VALID_ANSWERS
    WHERE VALID_ANSWERS.code in
    (
    SELECT ques.code
    FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
    WHERE ques.score_value_ind = 'S'
    and ques.tx_id = #ytd_eval1a.tx_id
    and ques.code = ans.code
    and ques.answer_value = ans.answer_value
    and ans.valid_answer <> 'NA')
    GROUP BY code
    )
    AS q

    thank you very much for any and all help!!!!!! I'm pulling my hair out on this one.

  4. #4
    Join Date
    Jun 2003
    Posts
    5
    Not really sure on that one, you may want to try rewriting your query to avoid the subselects. That might give you a fresh look on it and it is more effecient for sure.

    SELECT DISTINCT code, MAX(answer_value) AS maxValues
    FROM VALID_ANSWERS ans
    JOIN QUESTION_DATA ques
    ON ques.code = ans.code
    AND ques.answer_value = ans.answer_value
    JOIN #ytd_eval1a
    ON ques.tx_id = #ytd_eval1a.tx_id
    WHERE ques.score_value_ind = 'S'
    AND ans.valid_answer <> 'NA')
    GROUP BY code

    Good luck,
    Ryan

Posting Permissions

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