October 17, 2008 at 9:08 am
I have a customer Orders table with foreign exchange buy/sell orders. My Rates table gets multiple updates per second.
I want to join my Orders table with my Rates table based on the cur_1 and cur_2 columns.
I am trying to use an embedded CASE to pull the appropriate column value from Rates (i.e. the buy/sell of the order determines whether I pull the "ask" or the "bid" rate from the Rates table).
Here's what I have, but it does not seem to be working.
SELECT
DISTINCT
buy_sell=
CASE when ord.buy_sell=1 then 'Buy'
when ord.buy_sell=2 then 'Sell'
END,
ord.amount,
ord.amt_unit,
mkt_rate = -- calculate market 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,
-- pass mkt_rate value to function
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)
FROM Orders ord, users u, Rates r
WHERE ord.user_id = u.user_id AND u.client = @client
AND ord.client = @client
AND ord.cur_1 = r.cur_1
AND ord.cur_2 = r.cur_2
Now I did noticed this morning that I am NOT qualifying mkt_rate. Should I have ord.mkt_rate every time I reference my calulcated mkt_rate column ?
Please help.
Thank you in advance.
Bob
October 17, 2008 at 9:46 am
You must have a mkt_rate column in one of your tables that is being used in the function call because SQL will not let reference an alias name in the select list. So for readability and clarity I would qualify mkt_rate.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 10:13 am
If you have any doubt, you want to qualify the objects. You never know when you might need to modify something this complex and then create an error and waste time tracking it down.
Just qualify, you could be done in the time you've spent researching this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 17, 2008 at 11:20 am
So if I do qualify it as ord.mkt_rate, does this Select logic look good ?
And by the way, mkt_rate is in fact in my orders table.
Thanks,
Bob
October 17, 2008 at 11:32 am
Well the implementation of your logic looks okay, but because I don't know all the in's and out's of the system I can't tell you if the logic itself it right.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply