Professional Web Applications Themes

when / then syntax - Microsoft SQL / MS SQL Server

i am using the following syntax to combine two selects into one for performance purposes: SELECT premium_balance = SUM(CASE gl_account_id WHEN 20 THEN amount ELSE 0 END), markup_balance = SUM(CASE gl_account_id WHEN 13 THEN amount ELSE 0 END) FROM tGLDetail WHERE contract_id = contract_id AND gl_account_id in (13, 20) this works great but i dont understand the point of saying "ELSE 0" why not: "CASE gl_account_id WHEN 20 THEN amount END" however when i tried this i get the following warning: "Warning: Null value is eliminated by an aggregate or other SET operation." thanks much for your insight jt...

  1. #1

    Default when / then syntax

    i am using the following syntax to combine two selects
    into one for performance purposes:

    SELECT premium_balance = SUM(CASE gl_account_id WHEN 20
    THEN amount ELSE 0 END),
    markup_balance = SUM(CASE gl_account_id WHEN 13 THEN
    amount ELSE 0 END)

    FROM tGLDetail

    WHERE contract_id = contract_id
    AND gl_account_id in (13, 20)

    this works great but i dont understand the point of
    saying "ELSE 0"
    why not:
    "CASE gl_account_id WHEN 20 THEN amount END"

    however when i tried this i get the following warning:
    "Warning: Null value is eliminated by an aggregate or
    other SET operation."

    thanks much for your insight
    jt
    jt Guest

  2. #2

    Default Re: when / then syntax

    > this works great but i dont understand the point of 

    Because for any row where your CASE condition doesn't match, you will get
    NULLs, and these will be left out of the aggregate (which is what the
    warning says).


    Aaron Guest

  3. #3

    Default Re: when / then syntax

    jt wrote: 

    As you have just seen, using the ELSE avoids the warning and cleans up the
    code. In general, whether or not you use the ELSE depends on if you want a
    null or non-null value in that column when the case conditions are not met.
    This may or may not be important to you. Unless I explicitly want a null for
    some reason, I prefer to code the ELSE just to show that I have thought
    about the fall-through case and didn't just overlook it.

    --
    Steve Troxell
    Krell Software
    Professional Database Development Tools for MS SQL Server
    http://www.krell-software.com



    Steve Guest

  4. #4

    Default Re: when / then syntax

    here's a quick fix:

    SELECT premium_balance = SUM(CASE gl_account_id WHEN 20
    THEN isnull(amount,0) ELSE 0 END),
    markup_balance = SUM(CASE gl_account_id WHEN 13 THEN
    isnull(amount,0) ELSE 0 END)


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "jt" <net> wrote in message
    news:0c6c01c35d06$3417d570$gbl... 


    oj Guest

  5. #5

    Default when / then syntax

    when ever you use an aggregate function (sum,avg) on a
    particular column
    the query processor does not takes into consideration the
    rows which have the NULL values

    the following is an example for visualization:

    create table tab5
    ( field1 int)

    insert into tab5 VALUES (100)
    insert into tab5 VALUES (200)
    insert into tab5 VALUES (300)
    insert into tab5 VALUES (0)
    insert into tab5 VALUES (400)
    insert into tab5 VALUES (500)
    insert into tab5 VALUES (0)

    SELECT AVG(FIELD1) FROM TAB5
    SELECT SUM(FIELD1) FROM TAB5

    DELETE FROM TAB5

    insert into tab5 VALUES (100)
    insert into tab5 VALUES (200)
    insert into tab5 VALUES (300)
    insert into tab5 VALUES (NULL)
    insert into tab5 VALUES (400)
    insert into tab5 VALUES (500)
    insert into tab5 VALUES (NULL)

    SELECT AVG(FIELD1) FROM TAB5
    SELECT SUM(FIELD1) FROM TAB5

    for the aggregate function Avg(field1) u can find the
    difference.
    This the reason behind for firing the message.

    But I am not sure in which scenerio exactly the message
    gets fired.

    Hope your question who knows more about the internals of
    the query processor.


    Rajesh Peddireddy
     
    Rajesh Guest

Similar Threads

  1. if/else tag syntax
    By negttid in forum Macromedia Flex General Discussion
    Replies: 8
    Last Post: March 20th, 02:15 AM
  2. Help with SYntax, Please!
    By Artspan in forum Macromedia ColdFusion
    Replies: 3
    Last Post: April 15th, 06:23 PM
  3. Syntax
    By nonzero in forum UNIX Programming
    Replies: 4
    Last Post: September 13th, 08:17 PM
  4. Syntax help
    By Aaron in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 15th, 08:07 PM
  5. Help with DB2 Syntax.
    By sharads in forum IBM DB2
    Replies: 1
    Last Post: July 3rd, 09:04 AM

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