Professional Web Applications Themes

ISNULL(MyTextColumn, '') returns error in View editor - Microsoft SQL / MS SQL Server

I have a column of TEXT data type named MyTextColumn. When I try to create a view with this expression ISNULL(MyTextColumn, '') the View editor gives me an error that says, "Data type error in expression." The same clause works perfectly in a stored procedure or in Query yzer. I've also tried ISNULL(MyTextColumn, CONVERT(text, '')) but that doesn't work either. Is this a known bug? Thanks, Michael Carr...

  1. #1

    Default ISNULL(MyTextColumn, '') returns error in View editor

    I have a column of TEXT data type named MyTextColumn. When I try to create a
    view with this expression

    ISNULL(MyTextColumn, '')

    the View editor gives me an error that says, "Data type error in
    expression." The same clause works perfectly in a stored procedure or in
    Query yzer. I've also tried

    ISNULL(MyTextColumn, CONVERT(text, ''))

    but that doesn't work either.

    Is this a known bug?

    Thanks,
    Michael Carr


    Michael Guest

  2. #2

    Default Re: ISNULL(MyTextColumn, '') returns error in View editor

    Michael,

    I can't duplicate this. Can you post the actual DDL code for the table and view
    that have this problem, together with a row that errors?

    This all works:

    create table Txt
    (
    MyTextColumn text,
    SomeOtherCol varchar(30)
    )
    go
    insert Txt values ( null, 'ok' )
    go
    select ISNULL(MyTextColumn, 'Replaced')
    from Txt

    select coalesce( MyTextColumn, 'Replaced')
    from Txt

    create view MyTxt
    as select ISNULL(MyTextColumn, 'Replaced') as TxtCol, SomeOtherCol
    from Txt

    select * from MyTxt

    You may also want to try using coalesce instead - it's ANSI, and more flexible.

    Regards
    AJ


    "Michael Carr" <edu> wrote in message news:%phx.gbl... 


    Andrew Guest

  3. #3

    Default Re: ISNULL(MyTextColumn, '') returns error in View editor

    You want to use Query yzer for the view creation instead.

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


    "Michael Carr" <edu> wrote in message
    news:%phx.gbl... 



    oj Guest

Similar Threads

  1. SELECT ISNULL DATE
    By mikeap in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 29th, 06:25 PM
  2. How do I check for nulls? IsNull??
    By bflophil in forum Macromedia ColdFusion
    Replies: 0
    Last Post: February 18th, 11:22 PM
  3. IsNull bug in ASP?
    By Bob Cottis in forum ASP
    Replies: 2
    Last Post: October 15th, 12:31 PM
  4. Using ISNULL in Dynamic SQL
    By Barry in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 05:00 PM
  5. Wierd error when going to Design View from HTML view
    By VB Programmer in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 03:20 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