Inserting NULL

[jaydeeppal]

Hi all!
Can anybody tell me the various methods of inserting a NULL value to a particular col of a table.
Also clarify, if I move '-1' to the Null indicator of the host variable corresponding to that col, and then insert it, will it work?

Jaydeep.

[srinivas]

Hi,

Yes u r right, by moving -1 value to null-indicator we can insert null values.

[ovreddy]

Hi Srinivas,

In SPUFI like tool we can use directly NULL to insert null values..

Ex: INSERT INTO TABLE1(COL1,COL2) VALUES (102,NULL);

This will insert a null value into second column of the table.

Also we can use same type of syntax for UPDATE.

Thanks,
Reddy

[dbzTHEdinosauer]

careful, you speak of NULL as a value. It is not. Null is a state.
_________________
Dick Brenholtz
American in Varel, Germany

[ovreddy]

Hi,

I have executed the following statement and I didn't see any error in execution... If you are talking about wording then I will agree with you...

CREATE GLOBAL TEMPORARY TABLE TEST(EMPNO INTEGER,ENAME CHAR(10));
---------+---------+---------+---------+---------+---------+-----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
INSERT INTO TEST VALUES(10,'RAJU');
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
INSERT INTO TEST VALUES(20,NULL);
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
SELECT * FROM TEST WHERE ENAME IS NULL;
---------+---------+---------+---------+---------+---------+-----
EMPNO ENAME
---------+---------+---------+---------+---------+---------+-----
20 ----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Thanks,
Reddy

[dbzTHEdinosauer]

Reddy, you are correct: it is the wording. NULL is not inserted; the NULL Indicator
(an additional column added to the table when a data column has been declared as NULLABLE in the CREATE Table SQL)

for a data column of the DB2 table is SET.

When the NULL indicator for a column is SET to -1 (NULL) then nothing is associated with the data column; DB2 does not return anything for the data column nor does DB2 modify the data column. As you know, you cannot modify the data column unless the NULL Indicator for that column is set to NOT NULL (0 or <> -1).
_________________
Dick Brenholtz
American in Varel, Germany