Problem Note 42979: Using the DBKEY= option in the SQL procedure might cause incorrect results
Incorrect data values might result when you submit an SQL procedure that has all of the following attributes:
- You use the DBKEY= option.
- The key variable in the SAS data set contains a missing value.
- The key field in a DB2 table is defined as NOT NULL.
- The data type of the DB2 field is one of the following:
- REAL
- DOUBLE
- SMALLINT
- INTGER
- DECIMAL
- DATE
- TIME
- TIMESTAMP
In this situation, the values from the previous matched row are retained for the non-matched row as well.
This problem can occur if you run a query similar to the following:
proc sql;
select *
from work.test a
left join db2lib.testdbkey(dbkey=(id)) b
on a.id = b.id;
quit;
Values in SAS data set
name id
Steve .
Jan 1
Peter .
Kathy 4
Values in DB2 table
ID X
1 1
2 2
3 3
This query results in the following output:
Values in result
name id X
Steve . . /* <-- Correct: The value for X is missing here, as it should be. */
Jan 1 1
Peter . 1 /* <-- Incorrect: The value for X should be missing here, but it is not. */
Kathy 2 2
Currently, there is no solution for this behavior.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to DB2 | z/OS | 9.1 TS1M3 SP4 | 9.3 TS1M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | low |
| Date Modified: | 2011-04-19 15:03:54 |
| Date Created: | 2011-04-18 10:55:01 |
