Professional Web Applications Themes

CASE (x) when NULL doesnt work on OUTER joined fields? - Microsoft SQL / MS SQL Server

  FROM V  with L.id = R.id) and  values were not  case. Note: CASE is never happy unless all the possibilities evaluate to a compatible data type. I presume someField is numeric?...

Sponsored Links
  1. #1

    Default CASE (x) when NULL doesnt work on OUTER joined fields?

     
    FROM V 
    with L.id = R.id) and 
    values were not 
    case. Note: CASE is never happy unless all the
    possibilities evaluate to a compatible data type. I
    presume someField is numeric?
    Sponsored Links
    phil Guest

  2. #2

    Default Re: CASE (x) when NULL doesnt work on OUTER joined fields?

    The simple case function can only test equality, and as you cannot compare NULL with anything (Because it is unknown) It will never return true.

    Use the searched case function instead:

    SELECT
    CASE
    WHEN someField IS NULL THEN 0
    ELSE someField
    END
    FROM V


    "Zig Mandel" <z_man_del[remove underscores and this]mac.com> wrote in message news:phx.gbl...
    This appears to be a bug in sql server?

    CREATE VIEW VNoNULL AS
    SELECT CASE (someField) WHEN NULL THEN 0 ELSE someField FROM V

    V is a View of two tables that have been LEFT JOINed:
    CREATE VIEW V AS
    SELECT R.someField from L LEFT JOIN R ON L.id = R.id


    V.someField will sometimes be NULL (when there is no R with L.id = R.id) and
    I want to make it look like a zero.
    So I created VNoNULLs, but to my surprise, the <NULL> values were not
    getting converted to zero.

    Any ideas?

    Zig Mandel



    Damien Guest

  3. #3

    Default Re: CASE (x) when NULL doesnt work on OUTER joined fields?

    > SELECT CASE (someField) WHEN NULL THEN 0 ELSE someField FROM V

    How about

    SELECT COALESCE(someField, 0) FROM V


    Aaron Guest

Similar Threads

  1. NULL or no values in OUTER JOINS
    By Captain Special in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 20th, 04:17 PM
  2. &nbsp; and null fields in a datagrid
    By Tina in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: October 19th, 10:40 AM
  3. How insert null in numerics fields?
    By Vilmar Braz„o de Oliveira in forum ASP Database
    Replies: 5
    Last Post: January 8th, 06:40 PM
  4. Replies: 0
    Last Post: November 21st, 04:58 PM
  5. #26351 [NEW]: Incorrect handling of Null Fields/Numerical Fields with '0'
    By jabberwocky at ibplanet dot com in forum PHP Development
    Replies: 0
    Last Post: November 21st, 04:47 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