I'm having a strange problem with a table. I'm importing XML data into a loading table then inserting/updating to a final table. There are 20k+ rows in the final table, and 499 of them contain "blank" cost fields. Here is the query I use to determine this:
select cost from mytable where cost = ''
This shows data like this:
The cost field is a money datatype. The xml source data has a 0 for this field. During the import I use a data conversion task to convert everything from the xml file to "String [DT_STR]". The load table has cost setup as a varchar datatype. That's all I do as far as data conversion. The cost column does not have a default binding set or anything like that. However when queried it returns these 0.00 rows as blanks. They aren't being counted as NULL, just blank. This causes a big headache when trying to report off these rows and using functions like SUM, AVG, etc...
Any ideas why these specific rows are coming back as "blank", even though they show 0.00, and how to fix them so they return as 0?