September 10, 2008 at 10:20 am
I am calculating a value in a Select statement, then passing that calculated value into functions embedded within the same Select. Does this work ?
For example, I used to pull the market rate and pipsaway values by specifying "ord.mkt_rate" and "ord.pipsaway" in my query. However I would like to now calculate mkt_rate on-the-fly by embedding a function, then passing mkt_rate into the pipsaway() and Calc_Ord_Color() functions as follows :
Select ord.amount,
ord.amt_unit,
mkt_rate = dbo.get_mkt_rate( ,...),
pipsaway = dbo.Calc_Pips_Away(ord.ticket,ord.cur_1,ord.cur_2, mkt_rate),
color_string = dbo.Calc_Ord_Color('color',ord.ticket, ... , mkt_rate),
...
into #temp
FROM orders ord, filters flst, users u
WHERE
Will this technique work for me ? Will I be able to pass the correct mkt_rate value into the functions pipsaway() and calc_ord_color() or will mkt_rate default to the column value in the Orders table ?
Thank you in advance,
Bob
September 10, 2008 at 11:20 am
It will not work. That is really a way of specifying an alias for the column name, it is not an assignment statement. You will need to call the function again.
September 10, 2008 at 11:44 am
Thank you for your response. My problem is that I cannot call get_mkt_rate() multiple times because the market rates are moving much too fast. Sometimes I'll get slightly different mkt_rate values between calls to get_mkt_rates().
i.e. the EUR USD currency rate may move from 1.402 to 1.405 to 1.406, then back to 1.402 within less than a second on a busy day in the market.
Perhaps I need to create a temp table of the currency rates I need at that moment in time, then join that table in the subsequent Select statement which is mentioned in my first post.
Thanks again,
Bob
September 10, 2008 at 11:47 am
You could do that or possibly enable and use snapshot isolation - which would allow you to maintain a consistent version of the tables involved in your query without blocking other processes.
September 10, 2008 at 12:13 pm
you could just embed your primary query ...
Select nestsel.*,
mkt_rate = dbo.get_mkt_rate( ,...),
pipsaway = dbo.Calc_Pips_Away(nestsel.ticket,
nestsel.cur_1,nestsel.cur_2, nestselmkt_rate),
color_string = dbo.Calc_Ord_Color('color',nestsel.ticket, ... , mkt_rate),
into #temp
from (
Select ord.amount,
ord.amt_unit
...
FROM orders ord, filters flst, users u
WHERE ...
) nestsel
where ...
Be aware functions may actually be hidden joins !! :crazy:
Avoid hidden joins at all costs !!! They are very nasty to detect, perfmon, fine tune,...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2008 at 12:21 pm
You can do a work around that would allow that to be done. BUT performance would likely be terrible. In my experience inline funtions like that perform horribly.
Why can't you design the database such that the market rate is just a join to another table? Then you don't have to do the lookup function.
Work around code....
USE tempdb
GO
IF OBJECT_ID('fnFoo') IS NOT NULL
DROP FUNCTION fnFoo
GO
CREATE Function fnFoo
(
@val int
)
returns int
AS
BEGIN
declare @ret int
SET @ret = @val * 2
RETURN @ret
END
go
IF OBJECT_ID('fnFoo2') IS NOT NULL
DROP FUNCTION fnFoo2
GO
CREATE Function fnFoo2
(
@val int
)
returns int
AS
BEGIN
declare @ret int
SET @ret = @val * 3
RETURN @ret
END
GO
-- Get some data using Jeff's tally table create script... COOL!
IF OBJECT_ID('testit') IS NOT NULL
DROP TABLE testit
SELECT top 10000 IDENTITY(INT,1,1) AS val
INTO testit
FROM sysobjects so1, sysobjects so2
, sysobjects so3
GO
;WITH testnestedfunction(FirstLevel, val) AS
(
SELECT
tempdb.dbo.fnFoo(val) AS FirstLevel,
val
FROM testit
)
SELECT
tempdb.dbo.fnFoo2(FirstLevel) as secondlevel
FROM testnestedfunction
Gary Johnson
Sr Database Engineer
September 10, 2008 at 12:41 pm
"Why can't you design the database such that the market rate is just a join to another table? Then you don't have to do the lookup function."
It's because the client is using the bid/ask logic to pull the apprpriate market rate. So based on a "Buy Take Profit" order I may pull the bid column, otherwise I may pull the ask column.
Here's the code (note: I've already select lst_ask, lst_bid, lst_rate from my market rates table:
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
How would I join the appropriate value into my main select while embedding this logic ? i.e. that's why I decided to move this logic into a function...
Thanks,
Bob
September 10, 2008 at 1:40 pm
Actually, I'm now experimenting with calling the function mult. times as suggested by the gentlemen earlier:
Select ord.amount,
ord.amt_unit,
mkt_rate = dbo.get_mkt_rate( ,...),
pipsaway = dbo.Calc_Pips_Away(ord.ticket,ord.cur_1,ord.cur_2, dbo.get_mkt_rate( ,...)),
color_string = dbo.Calc_Ord_Color('color',ord.ticket, ... , dbo.get_mkt_rate( ,...)),
...
into #temp
FROM orders ord, filters flst, users u
WHERE ...
There's still a chance that get_mkt_rate() will return slightly different values, but not much as the logic prior to this (we were re-reading the mkt_rate value several levels below in another function).
Thanks,
Bob
October 1, 2008 at 11:01 am
Hi again,
Regarding the embedded qry you suggested recently, I'm getting an error when I execute my procedure in a Sql Query window:
"Column names in each table must be unique. Column name 'mkt_rate' in table '#temp' is specified more than once."
Keep in mind that mkt_rate, pipsaway, ord_stat and color_string are column names in my main order_fx table.
Also, if I comment out "ord.mkt_rate" in my inner query below sql tells me "invalid column mkt_rate". Yet it's not complaining about pipsaway, ord_stat nor color_string columns. I'm confused...
Select NestedSel.*,
mkt_rate = dbo.get_mkt_rate(NestedSel.cur_1,NestedSel.cur_2,NestedSel.order_type,
NestedSel.buy_cur, @fxo_bidask),
pipsaway = dbo.Calc_Pips_Away2(NestedSel.ticket,NestedSel.cur_1,NestedSel.cur_2,
NestedSel.buy_cur,NestedSel.order_type, ... , NestedSel.mkt_rate),
ord_stat = dbo.Calc_Ord_Color2('sort',NestedSel.ticket, NestedSel.client,
NestedSel.buy_sell, NestedSel.cur_1, ..., NestedSel.mkt_rate ),
color_string = dbo.Calc_Ord_Color2('color',NestedSel.ticket, NestedSel.client, ... ,
NestedSel.mkt_rate )
into #temp
from (
SELECT DISTINCT
buy_sell,
... ,
ord.mkt_rate , -- do I need this for primary query ?
FROM order_fx ord, filter_list flst, users u
WHERE status <> 'EXECUTED' ... AND u.client = @client AND ord.client = @client
) NestedSel/* end: Embedded primary query */
October 2, 2008 at 1:03 am
Your problem is you use
NestedSel.*
This part already contains a mkt_rate column.
You generate a new mkt_rate column right next using
mkt_rate = dbo.get_mkt_rate(NestedSel.cur_1,NestedSel.cur_2,NestedSel.order_type,
NestedSel.buy_cur, @fxo_bidask),
-- do I need this for primary query ?
Yes you do, because you use it in your higher function
color_string = dbo.Calc_Ord_Color2('color',NestedSel.ticket, NestedSel.client, ... ,
NestedSel.mkt_rate )
Rename one of both columns and you'll be fine. (for the column names ...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2008 at 8:07 am
I think we might be making mountains out of molehills. I don't know what your parameters are to dbo.get_mkt_rate, but can't you just call it in a subquery, with whatever identifier you pass into it? (maybe product number or something?):
SELECT
sq.amount,
sq.amt_unit,
sq.mkt_rate,
pipsaway = dbo.Calc_Pips_Away(sq.ticket, sq.cur_1, sq.cur_2, sq.mkt_rate),
color_string = dbo.Calc_Ord_Color('color', sq.ticket, ... , sq.mkt_rate),
FROM (
SELECT ord.amount,
ord.amt_unit,
mkt_rate = dbo.get_mkt_rate( ,...),
ord.ticket,
ord.cur_1,
ord.cur_2,
...
FROM orders ord, filters flst, users u
WHERE ... ) sq
into #temp
this sample code may change because i don't know what parameters you're passing into get_mkt_rate and I'm not sure of the level of granularity of your data.
October 2, 2008 at 9:18 am
Actually I was initially embedding the call to get_mkt_rate() in my query in three places, but I run the risk of getting back more than one unique value by doing this (i.e. The foreign currency markets often move very, very fast during volatile markets).
I just tried something different yesterday. I joined the returned values from get_mkt_rate() with a subset of customers orders and created a temp table. Then in my main query I joined this temp table again. It seems to be working out fine so far.
Thanks,
Bob
October 2, 2008 at 9:47 am
Could you assign the market rate to a variable then use that variable in you query?
declare @rate float
set @rate = select dbo.get_mkt_rate( ,...)
Select ord.amount,
ord.amt_unit,
mkt_rate = @rate,
pipsaway = dbo.Calc_Pips_Away(ord.ticket,ord.cur_1,ord.cur_2, @rate),
color_string = dbo.Calc_Ord_Color('color',ord.ticket, ... ,@rate),
...
into #temp
FROM orders ord, filters flst, users u
WHERE
October 2, 2008 at 10:06 am
"Rename one of both columns and you'll be fine. (for the column names ...)"
Actually I can't. If I rename this :
Select NestedSel.*,
mkt_rate = dbo.get_mkt_rate(...)
...
to
Select NestedSel.*,
mkt_rate2 = dbo.get_mkt_rate(...)
...
I get an error "Invalid column name 'mkt_rate2'" when I try to embed "NestedSel.mkt_rate2" in my lower function calls.
I know I'm totally missing something here. Sorry about that.
October 2, 2008 at 10:20 am
Maybe I'll just stick with my new idea which appears to be working :
Select distinct
dbo.get_mkt_rate(ord.cur_1,ord.cur_2,ord.order_type, ord.buy_cur, @fxo_bidask) mkt_rate,
ord.cur_1,ord.cur_2,ord.order_type, ord.buy_cur
into #TempRates
From order_fx ord
This gives me the exact mkt_rate value I need per unique type of order in my temp table.
then in my main query's Where clause I join the temp table :
Select
...
From FROM order_fx ord, users u, #TempRates tmpRates
Where
AND u.client = @client
AND ord.client = @client
AND ord.cur_1 = tmpRates.cur_1
AND ord.cur_2 = tmpRates.cur_2
AND ord.order_type = tmpRates.order_type
AND ord.buy_cur = tmpRates.buy_cur
I need to look at cur_1/cur_2/order_type/buy_cur because that combination of values determines whether I pull the bid or the ask price in my market rates table (i.e. EURUSD Buy Take Profit, EURUSD Sell Stop Loss, etc.).
Does that appear to make sense. So far I believe my app's order board is looking good..
thanks.
Bob
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply