Professional Web Applications Themes

variable question - Microsoft SQL / MS SQL Server

Is there a way to assign an expression in a SELECT statement to a variable, so that you can use this variable in another expression? Example ID CurrentAmount TotalAmount 1 100 100 2 200 200 3 300 300 4 400 400 Here is the query that I write against this table: SELECT ID, CurrentAmount + TotalAmount AS GrandTotal, CASE WHEN CurrentAmount + TotalAmount > 199 THEN 'Small Amount' WHEN CurrentAmount + TotalAmount > 399 THEN 'Medium Amount' ELSE 'Large Amount' AS AmountSize I want to rephrase the query so that it looks something like this: SELECT ID, CurrentAmount + TotalAmount ...

  1. #1

    Default variable question

    Is there a way to assign an expression in a SELECT
    statement to a variable, so that you can use this variable
    in another expression?

    Example

    ID CurrentAmount TotalAmount
    1 100 100
    2 200 200
    3 300 300
    4 400 400

    Here is the query that I write against this table:

    SELECT ID, CurrentAmount + TotalAmount AS GrandTotal, CASE
    WHEN CurrentAmount + TotalAmount > 199 THEN 'Small Amount'
    WHEN CurrentAmount + TotalAmount > 399 THEN 'Medium
    Amount' ELSE 'Large Amount' AS AmountSize

    I want to rephrase the query so that it looks something
    like this:

    SELECT ID, CurrentAmount + TotalAmount AS GrandTotal, CASE
    WHEN GrandTotal > 199 THEN 'Small Amount' WHEN GrandTotal 
    AmountSize

    Thanks for the help!

    Derek Ruesch
    Derek Guest

  2. #2

    Default Re: variable question

    you can use derived table for this
    Ex:

    select id, grandtotal,CASE
    WHEN GrandTotal > 199 THEN 'Small Amount' WHEN GrandTotal 
    AmountSize
    from
    (SELECT ID, CurrentAmount + TotalAmount AS GrandTotal from table)
    a --Derived table

    --
    -Vishal
    "Derek Ruesch" <com> wrote in message
    news:0d4001c36697$70e66cb0$gbl... 
    > AmountSize
    >
    > Thanks for the help!
    >
    > Derek Ruesch[/ref]


    Vishal Guest

  3. #3

    Default Re: variable question

    This is a common posting here. Basically, you don't understand that SQL
    is a set-oriented language where things happen "all at once" and not in
    a simple "left to right, first to last" order. Files are not tables;
    rows are not records and coloumns are not fields.

    Here is how a SELECT works in SQL ... at least in theory. Real products
    will optimize things when they can.

    a) Start in the FROM clause and build a working table from all of the
    joins, unions, intersections, and whatever other table constructors are
    there. The table expression> AS <correlation name> option allows you
    give a name to this working table which you then have to use for the
    rest of the containing query.

    b) Go to the WHERE clause and remove rows that do not pass criteria;
    that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE
    clause is applied to the working in the FROM clause.

    c) Go to the optional GROUP BY clause, make groups and reduce each
    group to a single row, replacing the original working table with the new
    grouped table. The rows of a grouped table must be group
    characteristics: (1) a grouping column (2) a statistic about the group
    (i.e. aggregate functions) (3) a function or (4) an expression made up
    of the those three items.

    d) Go to the optional HAVING clause and apply it against the grouped
    working table; if there was no GROUP BY clause, treat the entire table
    as one group.

    e) Go to the SELECT clause and construct the expressions in the list.
    This means that the scalar subqueries, function calls and expressions in
    the SELECT are done after all the other clauses are done. The AS
    operator can give a name to expressions in the SELECT list, too. These
    new names come into existence all at once, but after the WHERE clause,
    GROUP BY clause and HAVING clause has been executed; you cannot use them
    in the SELECT list or the WHERE clause for that reason.

    If there is a SELECT DISTINCT, then redundant duplicate rows are
    removed. For purposes of defining a duplicate row, NULLs are treated as
    matching (just like in the GROUP BY).

    f) Nested query expressions follow the usual scoping rules you would
    expect from a block structured language like C, Pascal, Algol, etc.
    Namely, the innermost queries can reference columns and tables in the
    queries in which they are contained.


    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: variable question

    Joe,

    That is a good summary. Thanks, I don't think I've ever seen seen it
    written out that way, but it sure explains why you have to use derived
    tables and have to do some things the way you do.

    Robert



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

Similar Threads

  1. variable question
    By YuccaWeb in forum Macromedia Flash Flashcom
    Replies: 9
    Last Post: October 11th, 03:34 PM
  2. Variable Creation Question
    By FusionRed in forum Macromedia ColdFusion
    Replies: 5
    Last Post: March 18th, 09:45 PM
  3. operand variable question
    By 3702 in forum Macromedia Flash
    Replies: 0
    Last Post: January 28th, 04:00 AM
  4. Global variable question question
    By Jason Giangrande in forum PHP Development
    Replies: 3
    Last Post: July 23rd, 08:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139