Focal Point
[CLOSED] db2 select fails after db2 insert

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2197089786

October 11, 2017, 03:14 PM
AprilC
[CLOSED] db2 select fails after db2 insert
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,


WebFOCUS 8.1.04
Windows, All Outputs
October 11, 2017, 04:13 PM
Francis Mariani
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)


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 11, 2017, 04:17 PM
Francis Mariani
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



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 11, 2017, 04:48 PM
AprilC
You are right! Of course Smiler

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.


WebFOCUS 8.1.04
Windows, All Outputs