NULL


NULL data type is used to represents that the value is absent in the column. All data types accept the NULL value. If the column declaration not having NULL indicator or only NULL, then the column will allows inserting NULL value.

CREATE TABLE with NULLable Columns Syntax -


CREATE TABLE Table name
       (Column1 data-type,
       Column2 data-type,
       Column3 data-type [NULL],
			.
			.
       Column-n data-type
       Primary key/foreign key column1,column2,….column-n,
       [LIKE reference table name]
    ON database-name.tablespace-name)

If the column declaration had NOT NULL, then the column will not allow inserting the NULL value in it.

CREATE TABLE with NOT NULL Syntax -


CREATE TABLE Table name
	(Column1 data-type NOT NULL,
	 Column2 data- type,
	 Column3 data- type NOT NULL,
		.
		.
	 Column-n data-type
	 Primary key/foreign key column1,column2….column-n,
	 [LIKE reference table name]
ON database-name.tablespace-name)

How the INSERT/UPDATE NULL to the table in the Program:


Inserting/updating of NULL to the database tables through the program can be done in similar way. Steps to update/insert the NULL to table are:

  • Need to move ‘-1’ to NULL indicator field of the host variable.
  • The NULL Indicator will be automatically generated while creating the DCLGEN for all the columns which are Nullable columns.
  • The NULL indicator will have same name as the columns host variable by adding –NI at the end and as S9(04) COMP.
  • Execute the INSERT/UPDATE like below.

INSERT NULL Syntax -


EXEC SQL
	INSERT INTO 
	Table-name (Column-1, 
		Column-2,
			… ,
		Column-n)
	VALUES (:Host-variable1
		:Host-variable1-NI, 
		:Host-variable2
		:Host-variable2-NI,
			…,:
		Host-variablen)
END-EXEC.

UPDATE NULL Syntax -


EXEC SQL
	UPDATE Table-name 
	SET Column-1 = :Host-variable1
                   :Host-variable1-NI,
		Column-2 = :Host-variable2
		           :Host-variable2-NI,
		    … ,
		Column-n = :Host-variablen
	WHERE Condition
END-EXEC.