Embedded CASE in my query

  • 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

  • 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.

  • 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.

  • 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

  • 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.

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

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