November 3, 2010 at 4:44 am
Hi all,
I am having some trouble with a cross apply and a scalar function.
Here is my code:
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
selectp.currency
frompolicies p
cross apply dbo.fnxrate(p.currency, 'eur', @val_date) as x
where1 = 1
and p.pol_id = @pol_cd
Running this code returns following error
Msg 208, Level 16, State 3, Line 6
Invalid object name 'dbo.fnxrate'.
However, running select dbo.fnxrate('usd', 'eur', '2009/03/31')
works fine.
Can anyone help me out of this maze?
Thanks a lot
November 3, 2010 at 6:19 am
Convert your scalar function to a table-valued function. From BOL:
The APPLY operator allows you to invoke a table-valued function...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 3, 2010 at 6:27 am
You don't need a tvf here.
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
select currency,
dbo.fnxrate(currency, 'eur', @val_date) as x
from policies
where pol_id = @pol_cd
N 56°04'39.16"
E 12°55'05.25"
November 3, 2010 at 6:34 am
SwePeso (11/3/2010)
You don't need a tvf here.
Quite true, but you might benefit from it a lot.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 3, 2010 at 7:33 am
SwePeso (11/3/2010)
You don't need a tvf here.
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
select currency,
dbo.fnxrate(currency, 'eur', @val_date) as x
from policies
where pol_id = @pol_cd
Silly me!!! Some colleague spoke about CROSS APPLY and I jumped into the pit!
Anyway thanks to both of you!
BTW, changing the function to a TVF is not the best solution here as it is used all over the DB.
October 28, 2014 at 8:22 am
CROSS APPLY has the advantage that the function is only called once for each possible value of the function parameters, rather than once for each row. Convert it to a table like this:
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
selectp.currency
frompolicies p
cross apply (select dbo.fnxrate(p.currency, 'eur', @val_date) as x) as q
where1 = 1
and p.pol_id = @pol_cd
(q is the alias for the "table", x is the field name)
October 28, 2014 at 9:22 am
rot-717018 (11/3/2010)
BTW, changing the function to a TVF is not the best solution here as it is used all over the DB.
Then create a new one and test for differences. You might benefit from performance and you could slowly change the code where the scalar function is used.
October 28, 2014 at 10:36 am
DaveBoltman (10/28/2014)
CROSS APPLY has the advantage that the function is only called once for each possible value of the function parameters, rather than once for each row. Convert it to a table like this:
declare @pol_cd char(18), @val_date datetime
set @pol_cd = 'xx000000'
set @val_date = '2010/06/30'
selectp.currency
frompolicies p
cross apply (select dbo.fnxrate(p.currency, 'eur', @val_date) as x) as q
where1 = 1
and p.pol_id = @pol_cd(q is the alias for the "table", x is the field name)
The simplest test I can think of suggests that this is not the case.
/*
WITH E1 AS (SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) e1 (n)),
E2 AS (SELECT b.n FROM E1 a, E1 b),
E4 AS (SELECT c.n FROM E2 a, E2 b, E2 c)
SELECT ID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1, n
INTO #Temp
FROM E4
CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #Temp (ID)
*/
DECLARE @ID INT, @n INT, @nMOD INT, @StartDate DATETIME
---------------------------------------------------------------------
-- no user function
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = n%3
FROM #Temp
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- standard call to iTVF
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = x.[mod]
FROM #Temp
CROSS APPLY dbo.IF_MOD (n) x
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- standard call to UDF in output list
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = dbo.UDF_MOD(n)
FROM #Temp
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
-- cross apply UDF
SELECT @StartDate = GETDATE()
SELECT
@ID = ID,
@n = n,
@nMOD = x.[MOD]
FROM #Temp
CROSS APPLY (SELECT [MOD] = dbo.UDF_MOD(n)) x
ORDER BY ID
SELECT DATEDIFF(MILLISECOND,@StartDate,GETDATE())
----------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy