Inserting New Field

AD DIN G NE W FIELD TO T HE E XISTI NG TABLE OR HOW NULL S A RE CR EA TED IN TH E TABLE ? “ZEMPLOYEE” is table with some records as shown below.

The fields of this table are MANDT, EMPNO, EMPNAME, EMPSAL. Add a new field “DEPT (CHAR10)” to this table, and then if we activate this table, one observation is it is done successfully. When the following two SELECT queries are executed, no records are retrieved even though many records satisfying the WHERE condition (General assumption what we make ). This is the SELECT query…. SELECT * FROM ZEMPLOYEE INTO IT_EMPLOYEE WHERE DEPT EQ SPACE. SELECT * FROM ZEMPLOYEE INTO IT_EMPLOYEE WHERE DEPT NE SPACE. Now the question arises, why are these queries failing to retrieve records properly ?????

Here is the solution : When we insert a new field to the already existing table (also the table contains some records).NULL values are automatically inserted by the system in that field because already the table contains some records. However NULL and SPACE are not the same.

The logical expression SQL condition is either "true, false, or unknown". The expression is UNKNOW if one of the columns involved in the database contains a NULL value and is evaluated with another comparison as IS NULL. A line is only included in the resulting set if the logical expression is true. So, instead of using SPACE (predefined data object), use IS NULL in WHERE condition.This helps to retrieve the required records. Hence, the Logical expression result is UNKNOWN for the above two queries. So that, NO RECORDS ARE RETRIEVED.

Lucid description about NULL Value : A data type is a set of the following values that can be represented: • • •

NULL value Special NULL value Non-NULL value

NULL Value : The data type NULL value (that is, an unspecified value) is a special value. Its relationship to any other value is always unknown. Special NULL Value :

A special NULL value is a special data type and is the result of arithmetic operations that lead to an overflow or a division by 0. The special NULL value is only permitted for output columns and for columns in the ORDER clause. If an overflow occurs in an arithmetic operation or a division by 0 at another point, the SQL statement is abnormally terminated. The comparison of a special NULL value with any value is always undefined. As far as sorting is concerned, the special NULL value is greater than all nonNULL values, but less than the NULL value. Non-NULL value : Character string, LONG column, number, date value, time value, time stamp value, BOOLEAN. SPACE : SPACE is a Character type and it is also a predefined data object (CHAR 1). To fetch all records whose DEPT value is NULL value, use the following query. SELECT * FROM ZEMPLOYEE INTO IT_EMPLOYEE WHERE DEPT IS NULL. Now two records are inserted into the table ZEMPLOYEE. Y626 RAJ Y627 RAM

Regards,

16000 23000

Raghavender Vadakattu.