|
View previous topic :: View next topic
|
| Author |
Message |
eHorizon.Andrew
New User
.jpg)
Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications
|
|
|
|
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 |
|
 |
zero
New User
Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad
|
|
|
|
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 |
|
|
zero
New User
Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad
|
|
|
|
| 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 |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications
|
|
|
|
Thanks so much..Zero (  ).I am trying this.... |
|
| Back to top |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications
|
|
|
|
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 |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007
Posts: 28
Location: Bank of communications
|
|
|
|
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
