I have a process where I need to do a db2 insert followed by various db2 selects. We do all sql passthroughs to retrieve data. The insert is working but when I try to do the select following the insert, it fails. If I remove the insert, the select works so I'm thinking it has be because I'm doing an insert first.

Here is my insert:
SQL DB2
INSERT INTO DB2.DW_WBF_LOG_ACTY (WBF_APP_CD, WBF_TAB_CD, WBF_ACTY_CD, LOG_TS, LOG_ACTY_OCR_NMBR, PFG_USER_ID, PURGE_DT)
VALUES (&APP_CD, &TAB_CD, &ACTY_CD, '&LOG_TS', &LOG_ACTY_OCR_NMBR, '&PFG_USER_ID', '&PURGE_DT');

It's just a basic insert of some logging data. I do want to mention that I always get this message after my insert:
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/INSERT
But I'm thinking it's more informational because the insert works and looks fine in the tables.

Then for my select, I'm trying to do just a basic read of a simple piece of data:
SQL DB2 PREPARE SQLOUT3 FOR
SELECT CNTRHD_NM
FROM DB2.C_PN_CNTR
WHERE DB2.C_PN_CNTR.PRFX_CD = 'A'
AND DB2.C_PN_CNTR.CNTR_NMBR = 99999
for FETCH ONLY OPTIMIZE for 1 row;
-RUN
TABLE FILE SQLOUT3
PRINT *
END
-RUN

When I try to execute this select following the insert, I get this error:
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT3

I wondered if using the prepare statement was causing me an issue so I coded the select without the prepare but I receive the same error.

Does anyone know if there is a trick to performing a select following an insert?

This message has been edited. Last edited by: AprilC,

Do you have a WebFOCUS END statement after the SQL INSERT?

SQL DB2
INSERT INTO DB2.DW_WBF_LOG_ACTY (WBF_APP_CD, WBF_TAB_CD, WBF_ACTY_CD, LOG_TS, LOG_ACTY_OCR_NMBR, PFG_USER_ID, PURGE_DT)
VALUES (&APP_CD, &TAB_CD, &ACTY_CD, '&LOG_TS', &LOG_ACTY_OCR_NMBR, '&PFG_USER_ID', '&PURGE_DT');
END
-RUN

(Yes, that's an informational message)

You also need an END statement for the SQL PREPARE:

SQL DB2 PREPARE SQLOUT3 FOR
SELECT CNTRHD_NM
FROM DB2.C_PN_CNTR
WHERE DB2.C_PN_CNTR.PRFX_CD = 'A'	
AND DB2.C_PN_CNTR.CNTR_NMBR = 99999
for FETCH ONLY OPTIMIZE for 1 row;
END
-RUN
TABLE FILE SQLOUT3
PRINT *
END
-RUN

You are right! Of course

I was missing that darn END statement on my Insert. This is my first time doing an insert and once I had it working, I was thinking it must be something else. Thanks for finding my issue.