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.
Inserting NULL
Moderators: Kalicharan, Moderator Group
NULL value
Hi,
Yes u r right, by moving -1 value to null-indicator we can insert null values.
Yes u r right, by moving -1 value to null-indicator we can insert null values.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
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
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
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
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).
(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
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page