[CLOSED] Pass Global Temp Table logic to DB2 via WF

August 13, 2012, 12:08 PM

I have SQL that I need to pass to a DB2 database via WebFOCUS. However, I do not know the FOCUS code that I need to put between the SQL statements. Can anyone help me determine where and what FOCUS code I need to include? Any help would be greatly appreciated.

Here is a mockup of the SQL I need to pass.

-* start code to connect to database
SQL DB2 SET SERVER CARSPROD1
SQL DB2
-* end code to connect to database

DECLARE GLOBAL TEMPORARY TABLE SESSION.CHNL
(CAR_N VARCHAR(64) ,
CARCODE VARCHAR(32),
CAR_I BIGINT )
WITH REPLACE NOT LOGGED
IN USER_TEMP ON COMMIT PRESERVE ROWS;

Insert into SESSION.CHNL
SELECT distinct A. CAR_N ,
A. CARCODE,
A. CAR_I
FROM CARTABLE A
WHERE a. CAR_I IN (123);

CALL ADMIN_CMD('RUNSTATS ON TABLE SESSION.CHNL');

SELECT w.sls_d,
w. CAR_I ,
Sum(w.SALE_AMT) AS sales,
Sum(w.TAX+_AMT) AS tax
FROM adw.SALES_TABLE w
INNER JOIN session.chnl x
ON x.CAR_I = w.CAR_I
WHERE w.sls_d BETWEEN '05/31/2012' AND '06/10/2012'
GROUP BY w.sls_d,
w.CAR_I ;

TABLE FILE SQLOUT
PRINT *
ON TABLE SET ONLINE-FMT EXL2K
END
-RUN
-EXIT

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