Re: A question of principle

dmoyer_at_gpu.com wrote:

>In <01bbc113$f9982de0$6b19d280_at_freh-nt-007>, "Bard" <bwskiles_at_adpc.purdue.edu> writes:
>>I am a DBA. We have several developers working with our setup of Oracle
>>Data Warehouses. The source of our data is a mainframe.
>>
>>The question of principle has to do with transferring mainframe practices
>>to the Oracle warehouse. Primarily, the use of "blanks" or "spaces" in
>>place of NULLs. I am, on principle, opposed to using blanks where nulls
>>ought to be. I find the blanks to be misleading and a misuse of Oracle. I
>>also believe it sets a poor precedence for future Oracle applications.
>>
>>I would appreciate any and all opinions. Am I being too uncompromising?
>>(If it's of any use, we are using the star-join modelling technique as
>>opposed to normalization.) Does anyone else out there allow only blanks
>>to be entered into a field? If so, how is it working? Anyone out there
>>replace blanks with nulls where there were already blanks? If so, did you
>>have trouble teaching the users the concept of null values?
>>
>>Thanks to any and all who have input on this!
>>
>>--brad skiles
>
>NULLS FOREVER ...
>
>Remember that in group functions nulls are ignored, blanks and zeroes are not.
>If this is a data warehouse decisions will be made on the data. If someone
>wants an average you would want the rows without valid values ignored, thus
>the use of nulls. Also the use of NVL function to convert any null to what you
>would like to see is very powerful.
>
>D. Scott Moyer, Jr. dmoyer_at_gpu.com (preferred for work)
>GPU Service Corporation dsmoyer_at_enter.net (preferred for home)
>Reading, PA
>

Go with NULLS. If for no other reason, they don't waste space.

If a value is really unknown, NULL is what it's supposed to be. Spaces are a known value - spaces. Null is an unknown value and is treated as such by all functions and operators.

If you're forced to go with spaces, make sure the fields are defined as CHAR, not VARCHAR. Otherwise every time you need to compare a column with something, you'll need to pad it with spaces.

--
Chuck Hamilton
chuckh_at_dvol.com

This message delivered by electronic sled dogs. WOOF!