IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

A puzzle about SQL statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Mon Sep 22, 2008 12:56 pm
Reply with quote

hey dude, I have a question on SQL statement, and I have been tired out by this puzzle.

Requirement:
I have three tables, which all will be used in my SQL. They are trade, trade_num, and trade _attr. In order, they contains summary info for trade, the detail amount for the trade and the account info owning the trade.

let me show you the table structure.
Table: Trade
Columns: Trade_ID, Fund_ID,Cusip_ID,Lot_num,Trade_DT and so on summary info...............
Primary key: Trade_ID and Fund_ID.


Table: Trade_num
Columns: Trade_ID, Fund_ID,Currency_CD,Account_book,Local_amt,Base_amt and so on...
Primary key: Trade_ID,Fund_ID,Currency_CD and Account_book.

Table: Trade_attr
Columns: Fund_ID,Account_book,Base_currency_cd, and so on about Fund info columns.
Primary key: Fund_ID.

let me state the requirement.

I need to join the Trade and Trade_num(may be twice) to get one combined record.

First we can get the specified rec from Trade by given Fund_ID and Trade_ID, then we can use this to get the records from Trade_num table, but there may one or two records will be retrieved,for there may be two different currency for one trade----Base Currency and Local Currency. In Trade_num, these two recs may contain a lot of the same info, but meanwhile they contain the key data differently, like Currency CD, and all local number and base number.

In trade _attr, we can get the Account_book and Base_currency_cd by the fund_id uniquely,which will be used in Trade_num table.

The output is requested to be one record containing Trade and Trade_num info(No matter there are two recs in Trade_num or one).
So when there are two recs in Trade_num, we need to combined them into "one". e.g. when Base currency is varying from Local currency, we need to pick out all base related colums with base currency and join all local related colums with local currency, which like a "one" Trade_num rec.

Took a look about following demo output.


Code:
Trade:
Fund_id    Trade_id           Trade_dt                 local_currency_cd...........

AAAA          071122141     20080112                        CAD         


Code:
Trade_num:
Fund_id       Trade_id       Currency_cd    Account_book 

Local_buy_AMT    Base_buy AMT   Local_gain_AMT   Base_gain_AMT

AAAA          071122141       USD                         A                   
       
     0               1000                     0                        200


AAAA          071122141       CAD                         A                       

   500                0                     100                         0



Code:
Trade_Attr:

Fund_id          Account_book      Base_currency_cd

AAAA                         A                               USD


So outpur should be:

Code:
Fund_id    Trade_id          Trade_dt    local_currency_cd............

Currency_cd      Account_book     Local_buy_AMT     Base_buy AMT
   
Local_gain_AMT   Base_gain_AMT.........


AAAA          071122141     20080112          CAD....

USD                        A                  500                1000

100                    200 ..............                       


Can any one give some suggestions on this, I will be kept waiting for any response. Thanks very much!
Back to top
View user's profile Send private message
zero

New User


Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad

PostPosted: Mon Sep 22, 2008 3:58 pm
Reply with quote

hi,
First I would like to give an idea, the, we can try the Query :

a) Since we are combining two records in trade_num table into one record, this can be achived like this :

First Let us take only the Unique Columns from trade_num table :

Fund_id Trade_id Currency_cd
AAAA 71122141  USD
AAAA 71122141 CAD 

Let's add a new column to the above tabe and do a self join on it.

instace A :
Fund_id Trade_id Currency_cd newcol
AAAA 71122141 USD 1
AAAA 71122141 CAD 1

instance B :
Fund_id Trade_id Currency_cd newcol
AAAA 71122141 USD 2
AAAA 71122141 CAD 2

to achive the above,

Code:
SELECT X.fund_id, X.trade_id, X.currency_id,
            Y.fund_id, Y.trade_id, Y.currency_id
FROM 
           (SELECT  fund_id, trade_id, currency_id, 1 AS "newcol"
            FROM    trade_num
            WHERE   fund_id = "AAAA"
              AND   trade_id = "71122141" ) AS X,
            (SELECT  fund_id, trade_id, currency_id, 2 AS "newcol"
             FROM     trade_num
             WHERE   fund_id = "AAAA"
                 AND   trade_id = "71122141" ) AS Y
WHERE X.fund_id = Y.fund_id
    AND X.trade_id = Y.trade_id
    AND X.currency_id <> Y.currency_id
    AND X.newcol = "1";

The output of the Above query will be :
AAAA 71122141  USD CAD 

Here, we currency ids are from two different records.
in the same way, by selecting other fields we can get them to.

Now that, we are able to make two records into one record.

Now, join the trade and Above one record.
Back to top
View user's profile Send private message
zero

New User


Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad

PostPosted: Mon Sep 22, 2008 4:00 pm
Reply with quote

Code:
SELECT P.Fund_id, P.Trade_id, P.Trade_dt, P.local_currency_cd,
       Q.localid, Q.localbuyamt, Q.basebuyamt, Q.localgainamt, Q.basegainamt
FROM   trade P,
(SELECT X.fund_id, X.trade_id, X.currency_id AS "localid", Y.currency_id,
        X.account_book,
       (X.Local_buy_AMT + Y.Local_buy_AMT)   as "localbuyamt"
       (X.Base_buy AMT + Y.Base_buy AMT)     as "basebuyamt", 
       (X.Local_gain_AMT + Y.Local_gain_AMT) as "localgainamt",
       (X.Base_gain_AMT + Y.Base_gain_AMT)   as "basegainamt"
 FROM 
      (SELECT  Fund_id, Trade_id, Currency_cd, Account_book, Local_buy_AMT, Base_buy AMT, Local_gain_AMT,Base_gain_AMT,
               1 AS "NEWCOL"
       FROM    trade_num
       WHERE   fund_id = "AAAA"
         AND   trade_id = "71122141" ) AS X,
      (SELECT  Fund_id, Trade_id, Currency_cd, Account_book, Local_buy_AMT, Base_buy AMT, Local_gain_AMT,Base_gain_AMT,
               2 AS "NEWCOL"
       FROM    trade_num
       WHERE   fund_id = "AAAA"
         AND   trade_id = "71122141" ) AS Y
  WHERE  X.fund_id = Y.fund_id
    AND  X.trade_id = Y.trade_id
    AND  X.currency_id <> Y.currency_id
    AND  X.newcol = "1") AS Q

WHERE  P.fund_id = Q.fund_id
  AND  P.trade_id = Q.trade_id
  AND  P.fund_id = "AAAA"
  AND  P.trade_id = "71122141";


Here, i assumed that the amount fields are sum of the data presented in two records.

Hope this helps,
...
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Mon Sep 22, 2008 7:04 pm
Reply with quote

Thanks so much..Zero ( icon_biggrin.gif ).I am trying this....
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Wed Sep 24, 2008 1:44 pm
Reply with quote

Hi, come again, Zero, thanks for your SQL.

I have another question, what if there is only one record in Trade_num table, in this case, your sql:

Code:
X.currency_id <> Y.currency_id


will blow up the result, so I need to concern about this uncertain situation also, thank you again.

Thinking........
Back to top
View user's profile Send private message
eHorizon.Andrew

New User


Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications

PostPosted: Thu Sep 25, 2008 8:05 am
Reply with quote

I am wondering that whether I can use two SQLs to handle this two condition, one for "Only one record in Trade_num", the other is "Local ^= base currency(two records)".....and then I use UNION SQL reserve word to combine this two .....

can anyone help me is there any effect by doing this?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Relate COBOL statements to EGL statement All Other Mainframe Topics 0
No new posts process statement for SUPREC, CMPCOLM... TSO/ISPF 4
No new posts SYNCSORT/ICETOOL JOINKEYS SORT Statem... DFSORT/ICETOOL 13
No new posts DFDSS COPY using Filter REFDT statement JCL & VSAM 2
Search our Forums:

Back to Top