Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

is it possible to create TWO temporary tables?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Wed Oct 07, 2009 6:50 am    Post subject: is it possible to create TWO temporary tables?
Reply with quote

Hi All,
I need to create a file with daily & MTD values side by side, something like this:
Code:

RELM     RELM NAME   QTY   RATE   CHARGE       QTY    RATE  CHARGE
AAAA     AAAAAAAAA   5.00  0.200  5.000       20.00  0.200  40.000
BBBB     BBBBBBBBB   1.00  0.200  8.000       15.00  0.200  60.000
CCCC     CCCCCCCCC   0.00  0.000  0.000       14.00  0.400  20.000
DDDD     DDDDDDDDD   2.00  0.100  3.000       30.00  0.300  30.000


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       
   ;                                                 

Code:
Code:
Back to top
View user's profile Send private message

darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Wed Oct 07, 2009 8:37 am    Post subject:
Reply with quote

Hi,
After several tries, I got the syntax:
Code:

WITH <TEMPTABLE1>     
     (                   
       <T1COLNAME1>       
     , <T1COLNAME2>       
     ,      .           
     ,      .           
     , <T1COLNAMEN>       
     )                   
  AS ( SELECT <COL1>     
            , <COL2>     
            ,    .       
            ,    .       
            , <COLN>     
         FROM <TABLENAME>
        WHERE <CONDITION>
     )                   
,
   <TEMPTABLE2>     
     (                   
       <T2COLNAME1>       
     , <T2COLNAME2>       
     ,      .           
     ,      .           
     , <T2COLNAMEN>       
     )                   
  AS ( SELECT <COL1>     
            , <COL2>     
            ,    .       
            ,    .       
            , <COLN>     
         FROM <TABLENAME>
        WHERE <CONDITION>
     )           
SELECT T1COLNAME1
    ,  T2COLNAME2
        .
        .
        .
FROM TEMPTABLE1
   , TEMPTABLE2
WHERE  <CONDITION>
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
No new posts copy SEQ to PDS and create stats steve-myers JCL & VSAM 5 Thu Apr 27, 2017 1:15 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us