About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Thursday, September 13, 2007

Null-ifying the World

    Do you remember the table used in previous post? In the respective case we provided all the values, what happens when a value is missing? How do we deal with such cases? For such cases we have the value NULL. Let's try to insert a NULL in one of the columns: CREATE TABLE Table1( ID int , [Year] int , Product varchar(30) , Price decimal(13,2) , Quantity decimal(13,3))     Then we insterted in the table test data, let's try to insert also a NULL for one of the values: INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, NULL, 199.23, 100) --checking table's content SELECT * FROM Table1     We can check for NULL values using the IS NULL clasuse: SELECT * FROM Table1 WHERE Product IS NULL If we don't like NULL values, we can replace them with other values, with the help of IsNull function. SELECT ID, [Year], IsNull(Product, 'missing value'), Price, Quantity FROM Table1     Let's try something else: SELECT ID, [Year], IsNull(Product, 'The Prouct value is missing! Please correct it!') Product, Price, Quantity FROM Table1     As you can see, because the maximum length of Product column is 30, the text is trimmed after 30 characters. In order to solve this issue, we can reduce the error message to maximum 30 characters or convert the Product type from varchar(30) to varchar(50), enough to hold the whole string "" SELECT ID, [Year], IsNull(Cast(Product as varchar(50)), 'The Prouct value is missing! Please correct it!') Product, Price, Quantity FROM Table1     For the next example we need to insert more NULL values. INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, 'Product 6', NULL, 100) INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, 'Product 7', 45.08, NULL) INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, 'Product 8', NULL, NULL) INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, NULL, 'Product 9', NULL, NULL)     When we need to test for NULL values in multiple columns we could write something like this: SELECT * FROM Table1 WHERE Price IS NULL OR Quantity IS NULL or, in a simpler way: SELECT * FROM Table1 WHERE IsNull(Price, Quantity) IS NULL These checks are useful when you need to check for two values which shouldn't be simultaneously NULL. For more columns with possible NULL values, when the values have the same type, we can rewrite the statements as follows: SELECT * FROM Table1 WHERE COALESCE([Year], Price, Quantity) IS NULL The NULL values can be replaced with values of the same type as the field: SELECT ID , [Year] , IsNull(Product, 'The Prouct value is missing!') Product , IsNull(Price, 0) , IsNull(Quantity, 0) FROM Table1 Now, coming back from where we started, the base table. If we don't want NULL values in our data, we have to declare the content as not being NULL: --droping the table DROP TABLE Table1 CREATE TABLE Table1( ID int NOT NULL , [Year] int NOT NULL , Product varchar(30) NOT NULL , Price decimal(13,2) NOT NULL , Quantity decimal(13,3) NOT NULL)     Now, when we try to insert a NULL value INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, NULL, 199.23, 100)     We get a specific error message: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'Product', table 'Learning.dbo.Table1'; column does not allow nulls. INSERT fails.     Another way is to use defaults, thus if a value was not provided, it will be replaced with the DEFAULT values: CREATE TABLE Table1( ID int NOT NULL , [Year] int NOT NULL , Product varchar(30) DEFAULT 'Unknown' , Price decimal(13,2) NOT NULL , Quantity decimal(13,3) NOT NULL)     Inserting some values in it: INSERT Table1(ID, [Year], Price, Quantity) VALUES (1, 2002, 199.23, 100) INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (1, 2002, NULL, 199.23, 100) --testing the output SELECT * FROM Table1     That's all for the moment, I have to work a little more on this post, I wanted intially to make a tutorial from it but as nobody reads it, there is no point to do that for the moment.

No comments: