This file will come from a single DB2 table.
I am thinking of doing a join, but I don't know how to do that since in the case of RELM CCCC, it has no daily trade but it has trades for the month, and thus must appear as zero for the daily.
With that, I thought of creating TWO temporary tables, and then select on them. However, i don't know ho, or if it is even possible.
I have not found any significant info regarding this in the manual,
and I have been looking for an hour already. I always just get how to create a GTT, which is not what I want. Any ideas how to?
Currently, for one table, I have (which won't make sense to use with only one table):
Code:
*********************** Top of Data ************
WITH DLY_OCC_BKRG_TBL
( DLY_BKR_SHRT_NME
, DLY_OPT_INSTRY_TYP
, DLY_CNTRCT_AMT
, DLY_RTE
, DLY_CHRG_AMT
)
AS (
SELECT EXEC_BKR_SHRT_NME
, CASE OPT_INSTR_TYP
WHEN 'E' THEN 'EQUITY'
WHEN 'I' THEN 'INDEX'
END AS INSTR_TYP
, SUM(TXN_QTY) AS CONTRACTS
, PER_UNIT_RTE
, SUM(THIRD_PRTY_AMT) AS CHARGES
FROM CCCB.GOBS_OCC_BRKRG_SMRY
WHERE DATE (PROCESS_TS) = '2009-10-01'
GROUP BY EXEC_BKR_SHRT_NME
, OPT_INSTR_TYP
, PER_UNIT_RTE
UNION
SELECT EXEC_BKR_SHRT_NME
, CASE OPT_INSTR_TYP
WHEN 'E' THEN 'EQUITY'
WHEN 'I' THEN 'INDEX'
END AS INSTR_TYP
, 0.00 AS CONTRACTS
, PER_UNIT_RTE
, 0.00 AS CHARGES
FROM CCCB.GOBS_OCC_BRKRG_SMRY
WHERE DATE (PROCESS_TS) BETWEEN '2009-09-01' AND
'2009-09-30'
AND EXEC_BKR_SHRT_NME NOT IN
(
SELECT DISTINCT(EXEC_BKR_SHRT_NME)
FROM CCCB.GOBS_OCC_BRKRG_SMRY
WHERE DATE (PROCESS_TS) = '2009-09-30'
)
GROUP BY EXEC_BKR_SHRT_NME
, OPT_INSTR_TYP
, PER_UNIT_RTE
HAVING ( SUM(TXN_QTY) <> 0
OR SUM(THIRD_PRTY_AMT) <> 0 )
)
SELECT DLY_BKR_SHRT_NME
, DLY_OPT_INSTRY_TYP
, DLY_CNTRCT_AMT
, DLY_RTE
, DLY_CHRG_AMT
FROM DLY_OCC_BKRG_TBL
;