Embedded CASE in my query

Question

Embedded CASE in my query

bob mazzo

SSCommitted

Points: 1684
More actions
October 17, 2008 at 9:08 am

#194764

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736468 More actions · Answer 2

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.

bob mazzo SSCommitted Points: 1684 More actions · Answer 3

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

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