When trying to insert records into a table I am getting the above error. This table is a rollup table designed to run each night and summarize the previous days worth of data so that when we run reports we can run against this table with smaller data as opposed to the main table with millions of records.

Any assistance as to why this error is occurring and how I can fix it would be appreciated. The following code below is what my report looks like.

-*****(Web Focus - 7.6.11)*****-

-SET &YESTERDAY = AYMD(&YYMD, -2, 'I8YYMD');
-SET &MONTHS = EDIT(&YESTERDAY,'$$$$99$$');
-SET &DAYS = EDIT(&YESTERDAY,'$$$$$$99');
-SET &YEARS = EDIT(&YESTERDAY,'9999$$$$');

-SET &FDATE = EDIT(&MONTHS | '/' | &DAYS | '/' | &YEARS);
-SET &FFDATE = EDIT (&FDATE | ' 12:00:00');

-SET &DATES=HHMMSS(TMSEC);
-SET &FTIME = EDIT (&DATES, '99:$99:$99');

-**********PROCESS_DATE******************
-SET &YESTERDAY2 = AYMD(&YYMD, 0, 'I8YYMD');

-SET &MONTHS2 = EDIT(&YESTERDAY2,'$$$$99$$');
-SET &DAYS2 = EDIT(&YESTERDAY2,'$$$$$$99');
-SET &YEARS2 = EDIT(&YESTERDAY2,'9999$$$$');

-SET &FDATE2 = EDIT(&MONTHS2 | '/' | &DAYS2 | '/' | &YEARS2);

-****************************************

-SET &YESDAY2 = EDIT(&YESTERDAY, '9999/99/99');

-SET &YESDAY3 = EDIT(&YESTERDAY2, '9999/99/99');

-SET &DATE1 = EDIT(&YESDAY2 | ' 00:00:00');
-SET &DATE3 = EDIT(&YESDAY2 | ' 23:59:59');
-SET &FFDATE2 = EDIT (&YESDAY3 | ' ' | &FTIME);


ENGINE SQLORA SET DEFAULT_CONNECTION MODSNJ1.WORLD
ENGINE SQLORA SET DATETIME OFF
SQL SQLORA
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
END


ENGINE SQLORA SET DEFAULT_CONNECTION MODSNJ1.WORLD
SQL SQLORA PREPARE SQLOUT FOR
SELECT '&DATE1' AS REPDATE,A.CLIENT_ID,A.SUBCLIENT_ID,B.CLIENT_NAME,B.SUBCLIENT_NAME,A.RECOMMEND,A.REPORT,
COUNT(A.OID) AS VOLUME,SUM(A.TOTAL) AS VALUE, A.CURRCD,A.VIRTBIN,'&FFDATE2' AS PROCESS_DATE
FROM TRANS_MASTER A, COP_MASTER B
WHERE A.CLIENT_ID = B.EBIT_CLIENT_ID
AND A.SUBCLIENT_ID = B.EBIT_SUB_CLIENT_ID
AND A.oid_date between '&DATE1'
and '&DATE3'

GROUP BY A.CLIENT_ID,A.SUBCLIENT_ID,B.CLIENT_NAME,B.SUBCLIENT_NAME,A.RECOMMEND,A.REPORT, A.CURRCD,A.VIRTBIN
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS #HLIST1 FORMAT SQLMSS
END
-RUN
-*-- Insert all the rows from the temporary table ----------
ENGINE SQLORA SET DEFAULT_CONNECTION MODSNJ1.WORLD
SQL
INSERT INTO DAILY_TRANS_VIRTBIN_SUMM_VIEW (CLIENT_ID,REPDATE,SUBCLIENT_ID,CLIENT_NAME,SUBCLIENT_NAME,RECOMMEND,REPORT,VOLUME,VALUE,CURRCD,VIRTBIN,PROCESS_DATE)
SELECT CLIENT_ID,'&DATE1',SUBCLIENT_ID,CLIENT_NAME,SUBCLIENT_NAME,RECOMMEND,REPORT,VOLUME,VALUE,CURRCD,VIRTBIN,'&FFDATE2' FROM #HLIST1
;
END
-RUN

quote:

DAILY_TRANS_VIRTBIN_SUMM_VIEW

Could it be that this table has an access file that states KEYS=0?

Did you check to make sure that there is data in the hold file?

Each day there is abotu 14-20K worth in records