| View previous topic :: View next topic |
| Author |
Message |
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
 Posted: Mon Oct 06, 2008 4:12 am Post subject: Need help in writing Query |
 |
|
This is my condition.
I got fx_table which maintains the details of all the foreign exchange rates. It has a column called as from_currency, to_currency, fx_rate and effective_timestamp. The to_currency is always USD. I will use a cursor to fetch today's transactions fom the transaction table tran_table and if the used currency of transaction(used_currency) is australian dollar then i need to multiply the transaction amount with the latest fx_rt..
something like this.
| Code: | SELECT
tran_number,
used_currency,
case used_currency
when 'USD' then tran_amt
else cast(tran_amt * (select fx_rate
from fx_table
where to_currency = 'USD'
and from_currency = used_currency
and effective_timestamp =
(select max(effective_timestamp)
from fx_table
where to_currency = 'USD'
and from_currency = used_currency)
)
end as total_amt
FROM
tran_table
|
Can someone help..
_________________
Thanks |
|
| Back to top |
|
 |
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Mon Oct 06, 2008 11:06 am Post subject: |
|
|
Here is my guess.
Note:
1 Since to_currency is always USD, it is not checked
2 From you query it looked taht you want to do for all non USD curency, not only just AUD
3 It does not check if select is empty or not, you need to handle that
4 I am also learning DB2 so let me know what mistakes you find.
| Code: | update tran_table tt
set tran_amt = tt.tran_amt *
(select fx_rate from fx_table ft
where ft.from_currency = tt.used_currency
and ft.effective_timestamp =
(select max(effective_timestamp)
from fx_table ft2
where ft2.from_currency = tt.used_currency
)
)
where tt.from_currency <> 'USD' |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Oct 06, 2008 1:04 pm Post subject: |
|
|
edkir98,
Untested sql
| Code: |
SELECT A.TRAN_NUMBER
,A.USED_CURRENCY
,CASE A.USED_CURRENCY
WHEN 'AUS' THEN A.TRAN_AMT * B.FX_RATE
ELSE A.TRAN_AMT
END
FROM tran_table A
,(SELECT FX_RATE
FROM fx_table
WHERE TO_CURRENCY = 'USD'
AND FROM_CURRENCY = 'AUS'
AND EFFECTIVE_TIMESTAMP = (SELECT MAX(EFFECTIVE_TIMESTAMP)
FROM fx_table
WHERE TO_CURRENCY = 'USD'
AND FROM_CURRENCY = 'AUS'))B
; |
Dibakar,
| Quote: |
1 Since to_currency is always USD, it is not checked
2 From you query it looked taht you want to do for all non USD curency, not only just AUD
3 It does not check if select is empty or not, you need to handle that
4 I am also learning DB2 so let me know what mistakes you find.
|
1. The object is to get all the rows from the transaction table and only if the used currency is an aussie dollar it needs to be multiplied with exchange rate
2. You are trying to update the table where as OP wants a select |
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Mon Oct 06, 2008 4:54 pm Post subject: |
|
|
Kolusu,
I wrongly thought that Ed is trying to update.
Thanks,
Diba. |
|
| Back to top |
|
|
edkir98
Beginner
Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
|
| Posted: Mon Oct 06, 2008 11:21 pm Post subject: Re |
|
|
hi Kolusu and diba.
thanks for the help
Kolusu,
| Quote: | | The object is to get all the rows from the transaction table and only if the used currency is an aussie dollar it needs to be multiplied with exchange rate |
i think i missed a point..the issue is used_currency may be anything, something out of all the currencies in the world. So the used_currency for a transaction has to be queried on to the fx_rate table as a record is fetched from the transaction table.
diba,
the query you had used would not work..because i cannot use an output variable from a select clause directly onto an operand for multiplication operator..even that was the reason my query dint work.. it would give an sqlerror.
i've found a solution bt am not sure if it can be written effectively..
| Code: | SELECT
x.tran_number,
x.used_currency,
case x.used_currency
when 'USD' then x.tran_amt
else cast(x.tran_amt * y.fx_rate) as bigint
end as total_amt
FROM
tran_table X,
( SELECT A.from_currency
,A.fx_rate
FROM fx_rate A
,(SELECT B.from_currency
,MAX(effective_timestamp) AS effective_timestamp
FROM fx_rate B
WHERE B.to_currency = 'USD'
AND B.from_currency <> 'USD'
GROUP BY B.from_currency
) AS C
WHERE A.to_currency = 'USD'
AND A.from_currency <> 'USD'
AND A.from_currency = c.from_currency
AND A.effective_timestamp = C.effective_timestamp ) Y |
_________________
Thanks |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
