October 2, 2008 at 10:22 am
Boy do I wish I could do that ! The problem is the type of currency order determines whether I pull the bid or ask value for that currency pair from the market rates table (EURUSD Bull Take Profit, USDJPY Sell Stop Loss, etc).
Thanks,
Bob
October 3, 2008 at 6:22 am
if @order_type = 'SLO'
set @mkt_rate = @lst_ask
else
if @order_type = 'SLB'
set @mkt_rate = @lst_bid
else
if @order_type = 'CALL'
set @mkt_rate = @lst_rate
else
if @order_type = 'TP'
if @buy_cur = @cur_1
set @mkt_rate = @lst_bid
else
set @mkt_rate = @lst_ask
else
if @order_type = 'SL'
if @buy_cur = @cur_1
set @mkt_rate = @lst_ask
else
set @mkt_rate = @lst_bid
...
bob mazzo (10/2/2008)
Boy do I wish I could do that ! The problem is the type of currency order determines whether I pull the bid or ask value for that currency pair from the market rates table (EURUSD Bull Take Profit, USDJPY Sell Stop Loss, etc).Thanks,
Bob
If that's the case, then can't you use a CASE statement inside your SELECT instead of the function call?
Also, using a CTE instead of the nested select may give you better performance.
October 3, 2008 at 9:05 am
Great idea! I have CASE statements in other areas of the query, so I don't know why I didn't try that before. Let's see if this makes sense...
Now I calculate mkt_rate with a CASE, then reuse mkt_rate below in my function call to Calc_Pips_Away2(..) . Makes sense ?
mkt_rate =
CASE
WHEN ord.order_type = 'SLO' THEN r.lst_ask
WHEN ord.order_type = 'SLB' THEN r.lst_bid
WHEN ord.order_type = 'CALL' THEN r.lst_rate
WHEN ord.order_type = 'TP' THEN
CASE WHEN ord.buy_cur = ord.cur_1 THEN r.lst_bid ELSE r.lst_ask END
WHEN ord.order_type = 'SL' THEN
CASE WHEN ord.buy_cur = ord.cur_1 THEN r.lst_ask ELSE r.lst_bid END
ELSE r.lst_rate
END,
pipsaway =
dbo.Calc_Pips_Away2(ord.ticket,ord.cur_1,ord.cur_2,ord.buy_cur,ord.order_type,
ord.status, ord.invert, ord.fx_spot,
mkt_rate),
Now the value of mkt_rate will be consistent in all embedded function calls, correct ? (i.e. I have two additional function call that use mkt_rate again).
Thanks again !
Bob
October 3, 2008 at 12:33 pm
I was thinking something like this.
But it is not tested because you have not provided any sample data. 🙂
Also, keep in mind that you can later join on the CTE itself.
;WITH CTE_NAME AS
(
SELECT ord.amount,
ord.amt_unit,
mkt_rate =
CASE
WHEN ord.order_type = 'SLO' THEN r.lst_ask
WHEN ord.order_type = 'SLB' THEN r.lst_bid
WHEN ord.order_type = 'CALL' THEN r.lst_rate
WHEN ord.order_type = 'TP' THEN
CASE WHEN ord.buy_cur = ord.cur_1 THEN r.lst_bid ELSE r.lst_ask END
WHEN ord.order_type = 'SL' THEN
CASE WHEN ord.buy_cur = ord.cur_1 THEN r.lst_ask ELSE r.lst_bid END
ELSE r.lst_rate
END,
ord.ticket,
ord.cur_1,
ord.cur_2,
...
FROM orders ord...
JOIN filters flst...
JOIN users u...
WHERE ...
)
SELECT
CTE_NAME.amount,
CTE_NAME.amt_unit,
CTE_NAME.mkt_rate,
pipsaway = dbo.Calc_Pips_Away(CTE_NAME.ticket, CTE_NAME.cur_1, CTE_NAME.cur_2, CTE_NAME.mkt_rate),
color_string = dbo.Calc_Ord_Color('color', CTE_NAME.ticket, ... , CTE_NAME.mkt_rate),
FROM CTE_NAME
October 3, 2008 at 2:31 pm
Thanks for that idea, too.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply