News
Abstract
Return ROW_COUNT on CREATE TABLE and DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > Return ROW_COUNT on CREATE TABLE and DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA
When the WITH DATA clause is used to insert rows into the new table, the application will be able to retrieve the number of rows inserted in two ways:
- By using the GET DIAGNOSTICS statement and extracting the ROW_COUNT.
- By directly accessing the SQLERRD3 field within the SQLCA.
CREATE PROCEDURE PRODLIB.CreateSalesTable(out rowCount integer)
LANGUAGE SQL
BEGIN
/*
Build a temporary table with all the sales transactions from the current quarter.
Return the number of rows in the temporary table to the caller.
*/
DECLARE GLOBAL TEMPORARY TABLE SESSION.QuarterlySales as
(SELECT * FROM PRODLIB.SALES WHERE
QUARTER(SALES_DATE) = QUARTER(CURRENT DATE) AND
QUARTER(SALES_DATE) = QUARTER(CURRENT DATE) AND
YEAR(SALES_DATE) = YEAR(CURRENT DATE) ) WITH DATA WITH REPLACE;
GET DIAGNOSTICS rowCount = ROW_COUNT;
END
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167694