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.
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.
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.
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?
.jpg)
).I am trying this....