We help IT Professionals succeed at work.

Change a null value to false in a view

snyperj
snyperj asked
on
Hello- I have a bit field in a view that returns either true, false or NULL.

How can I transform any null value to be treated as 'false'?

Thanks
Comment
Watch Question

Ephraim WangoyaSoftware Engineer

Commented:
you could use case statement for that

select case when field is null then false else field end , .....

Author

Commented:
Where would I insert that in the view? (using management studio) in the 'Column' field????

Also, please help with the syntax... the field name is 'auto_taxed'

Thanks

Commented:
What about just doing this instead of a case statement?

SELECT COALESCE(auto_taxed,0) AS auto_taxed

Author

Commented:
Where does that get inserted in the view?  I am not understanding where to put that.  I need to incorporate it into an existing view that has several joins.  If I put that in the 'Column' field, Management Studio automatically surrounds it with quotes and it just returns the whole sql statement as a string in the view results.  Help!

Commented:
Please provide the query being used in the view.
Commented:
You can put it the definition of the view. While creating view, you do run select statement. Just you need to change that. You can alter your view as:-

alter view <viewname>
as
select
     t1.<col_1>,
     t1.<col_2>,
     value = case when t1.<col_3> is null then 'NULL'
                  else when t1.<col_3> = 0 then 'False'
                  else 'True'
                  end
   t1.<col_4>,
...
...
..
from <tablename> as t1

Author

Commented:
worked it out... thanks