I sort of want to get a "best practices" perspective regarding numeric values. First, I'll give my scenario:
In my SSIS package, a CSV file is used for data extraction. Once extracted, a staging table is loaded with all the values from the CSV. One column, called "IPI_Value" is a numeric (18,2) type. However, some of the data being loaded contain the value 'x' if there is no numeric value in the dataset. Obviously I can't store 'x' in a numeric field within the database so I came up with:
update a set a.IPI_Value = REPLACE(a.IPI_Value,'x','0') from dbo.tblIPIStaging a where a.IPI_Value='x'
The question I have is whether or not the value being replacing the 'x' should be a '0' or NULL. A 0, in my opinion can be misleading when analysing the data afterwards as the 'x' represents a non-value for a given timeframe. So what is the best practice then, replacing with 0 or a NULL? Or does it not really matter.
I know at query time the query can rule out either values with 0s or NULLs so....
Anyways, the people of this forum always give me the best advice so I anticipate your suggestions.