Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joseph1975
Starting Member
6 Posts |
 Posted - 2012-02-03 : 12:03:42
|
| I have declared a coloumn as Money datatype. Its displaying NULL when I do a Select *. should it be 0.00 rather than NULL. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-03 : 12:07:04
|
you need to modify column to be of NOT NULL type with DEFAULT value of 0.00ALTER TABLE <tablename> ALTER COLUMN <columnname> money NOT NULL DEFAULT 0.00 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-03 : 13:13:25
|
Or in your SELECT statement use:SELECT COALESCE(MyMoneyColumn, 0.0) AS MyMoneyColumn, ... you can use IsNull() instead of COALESCE(), but watch out how it handles the datatyping of the second parameter (thus I think that COALESCE() might be "safer", but I do realise that most people use IsNull() in such circumstances) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-03 : 13:34:42
|
quote:
Originally posted by Kristen
Or in your SELECT statement use:SELECT COALESCE(MyMoneyColumn, 0.0) AS MyMoneyColumn, ... you can use IsNull() instead of COALESCE(), but watch out how it handles the datatyping of the second parameter (thus I think that COALESCE() might be "safer", but I do realise that most people use IsNull() in such circumstances)
what will you do for existing records in table without value for the money column?  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-03 : 14:25:38
|
| I'm missing something Visakh as I thought this was a display issue, right? Or did I miss something? (other than the use of "SELECT *" which is bad for lots of reasons ....) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-03 : 16:02:53
|
quote:
Originally posted by Kristen
I'm missing something Visakh as I thought this was a display issue, right? Or did I miss something? (other than the use of "SELECT *" which is bad for lots of reasons ....)
I thought seeing thisshould it be 0.00 rather than NULL.OP was expecting to see table values as 0.00 rather than NULL ie default value. Thinking again, I'm not fully sure what OP is after  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-03 : 17:47:28
|
| "I'm not fully sure what OP is after"It won't be the last time - for either of us! |
|
|
|
|
|
|
|
|
Money datatype