| View previous topic :: View next topic |
| Author |
Message |
pai_sunil_bs
Beginner
Joined: 19 Sep 2005
Posts: 21
Topics: 11
|
 Posted: Sat Jun 17, 2006 7:10 am Post subject: to retrieve using a nullable column |
 |
|
to test for a null column value, we use the IS NULL comparison operator.
But my requirement is I need to retrieve data regardless of what value is stored (Null/Not Null).
I am coding it this way.
EXEC SQL
SELECT LASTNAME
INTO :PGM-LASTNAME
FROM EMP
WHERE (PHONENO IS NULL OR PHONENO =
:WS-PHONENO
END-EXEC.
Is this correct ? Or is there any better way to handle. |
|
| Back to top |
|
 |
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Mon Jun 19, 2006 2:33 am Post subject: |
|
|
Sunil,
Suppose you had created two tables with the following definations:
| Code: |
CREATE TABLE DEPT(DEPT_NO INTEGER NOT NULL, NAME CHAR(20),
PRIMARY KEY(DEPT_NO));
CREATE UNIQUE INDEX D1 ON DEPT(DEPT_NO);
CREATE TABLE EMP(EMPNO INTEGER NOT NULL, NAME CHAR(20),DEPT_NO INTEGER,
FOREIGN KEY(DEPT_NO) REFERENCES DEPT ON DELETE SET NULL);
|
And you inserted values into the two tables as below:
| Code: |
INSERT INTO DEPT VALUES(1, 'RESEARCH');
INSERT INTO DEPT VALUES(2, 'DEVELOPMENT');
INSERT INTO DEPT VALUES(3,'ADMIN');
INSERT INTO EMP VALUES(1,'ADEL', 1);
INSERT INTO EMP VALUES(2, 'JIM', 2);
INSERT INTO EMP VALUES(3,'BILL', 3);
|
And you selected the values from the tables:
| Code: |
SELECT * FROM DEPT;
DEPT NAME
----------- --------------------
1 research
2 development
3 admin
SELECT * FROM EMP;
EMPNO NAME DEPTNO
----------- -------------------- -----------
1 adel 1
2 jim 2
3 bill 3
|
Now if you deleted using query below:
| Code: |
delete from dept where name = 'research';
select * from dept;
DEPT NAME
----------- --------------------
2 development
3 admin
|
Now select from EMP table will retreive:
| Code: |
SELECT * FROM EMP;
EMPNO NAME DEPTNO
----------- -------------------- -----------
1 adel -
2 jim 2
3 bill 3
|
Notice the value of DEPTNO for employee 1 is set to null (-) as dictated by the constraint.
Now if you use the query below:
| Code: |
SELECT * FROM EMP WHERE DEPT_NO IS NULL;
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO NAME DEPT_NO
---------+---------+---------+---------+---------+---------+---------+---------+
1 ADEL -----------
SELECT * FROM EMP WHERE DEPT_NO IS NOT NULL;
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO NAME DEPT_NO
---------+---------+---------+---------+---------+---------+---------+---------+
2 JIM 2
3 BILL 3
|
If you want to retrieve regardless of NULL / NOT NULL values simply use
| Code: |
SELECT * FROM EMP;
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO NAME DEPT_NO
---------+---------+---------+---------+---------+---------+---------+---------+
1 ADEL -----------
2 JIM 2
3 BILL 3
|
Hope this helps.
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
pai_sunil_bs
Beginner
Joined: 19 Sep 2005
Posts: 21
Topics: 11
|
| Posted: Mon Jun 19, 2006 3:45 am Post subject: |
|
|
Assume you are getting DEPT_NO values from some other table which may or may not have values.
Say assume you are getting values..3,NULL from a table X. Now You need to get the NAME from EMP table.
Also There are other Columns as well. So you will get only one row even if you use Null in the where clause. Hope we understand each other. |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Mon Jun 19, 2006 4:22 am Post subject: |
|
|
In your code example the INTO clause is the problem. If the data you want to retrieve can have a NULL value, you have to provide an \"Indicator variable\", which will hold the value of the NULL-indicator (ie -1 if the value is NULL, 0 if it is set).
For the definition and use of such an indicator variable, please look into the programming guide manual of DB2.
The WHERE clause of your statement was ok.
regards
Christian |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Mon Jun 19, 2006 7:16 am Post subject: |
|
|
Sunil,
Look at the code below to to have better understanding about coding for Null values in the SQL.
| Code: |
WORKING-STORAGE SECTION.
01 WS-LITERALS.
05 ABEND-SQLCODE PIC S9(09) SIGN LEADING
SEPARATE DISPLAY.
05 NULL-IND PIC S99 COMP VALUE +0.
05 WS-DED-AMT PIC S9(09)V9(02) COMP-3.
******************************************************************
* EXECUTE SELECT QUERY *
******************************************************************
EXEC SQL
SELECT DED_AMT
INTO :WS-DED-AMT:NULL-IND
FROM TAXTABLE
WHERE EMPL_SERIAL = :WS-EMPNO
END-EXEC.
EVALUATE SQLCODE
WHEN 0
IF NULL-IND = -1
DISPLAY 'WS-DED-AMT ' WS-DED-AMT
END-IF
WHEN 100
CONTINUE
WHEN OTHER
MOVE 'SELECT FAILED FOR TAXTABLE' TO ABEND-MSG
MOVE SQLCODE TO ABEND-SQLCODE
MOVE WS-EMPNO TO ABEND-EMPNO
PERFORM 999-ABEND-ERROR THRU 999-ABEND-ERROR-EXIT
END-EVALUATE.
|
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
pai_sunil_bs
Beginner
Joined: 19 Sep 2005
Posts: 21
Topics: 11
|
| Posted: Mon Jun 19, 2006 7:29 am Post subject: |
|
|
Thanks Shekar.
But I am not talking about the INTO clause.
I am talking about the usage in the WHERE clause.
In your Example:-
It will be WS-EMPNO. If WS-EMPNO has value then No issues. But what if WS-EMPNO has Null Values?
I hope you got the question right.
Just assume that in your WHERE clause you have one more COLUMN say EMP_DOB. Assume that the below query returns ONLY 1 row in any case.
EXEC SQL
SELECT DED_AMT
INTO :WS-DED-AMT:NULL-IND
FROM TAXTABLE
WHERE EMPL_SERIAL = :WS-EMPNO
AND EMP_DOB = :WS-DOB
END-EXEC.
Suppose, Emp no is Unkown but you know WS-DOB..The above query will NOT work.
Hope I got my question right. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
