I have an external Oracle db that has records of historical customer comments. I am attempting to pull data for a specific set of call records. In the table, each record number can have multiple comment_types, but only one row of each comment_type for each record number. My index on this table is as such, (CALL_NO, COMMENT_TYPE). If I run this query below, and substitute the "(SELECT b.CALL_NO FROM LIB.STAGE2 b WHERE b.Row between 10001 and 20000) with just a hard coded list of 10000 comma delimited call numbers the query runs perfectly. I have to limit this query to run 10,000 calls at a time, it returns results within seconds, however if I go even 1 call number over it locks up. I believe it is a limit on the external database. I have verified that this select statement results in a list of unique CALL_NO's, identical to the one I generate to hard code. With the above SELECT statement in place as seen below, the query will time out and fail. PROC SQL;
libname LIB_O meta library="ORACLE LIB" METAOUT=DATA;
INSERT INTO LIB.COMMENT_TEMP
SELECT a.CALL_NO, a.COMMENTS, a.CREATE_DATE, a.CREATE_USERID, a.UPDATE_DATE, a.UPDATE_USERID, a.COMMENT_TYPE
FROM LIB_O.COMMENT a
WHERE a.CALL_NO IN (SELECT b.CALL_NO FROM LIB.STAGE2 b
WHERE b.Row between 10001 and 20000)
And
a.COMMENT_TYPE IN ("PROB", "REM" , "DIR1", "DIR2", "DIR3", "DIR4", "DIR5", "DIR6", "DIR7", "DIR8");
QUIT; Why does this query act so differently, if the results of the SELECT statement are the same as hard coding the CALL_NO's?? Judging by the way the log reads, it doesn't seem to be observing the external database using the index, but as two separate observations. NOTE: There were 990500 observations read from the data set LIB_O.COMMENT.
WHERE COMMENT_TYPE in ('DIR1', 'DIR2', 'DIR3', 'DIR4', 'DIR5', 'DIR6', 'DIR7', 'DIR8', 'PROB', 'REM');
NOTE: There were 10000 observations read from the data set LIB.STAGE2.
WHERE (Row>=10001 and Row<=20000);
... View more