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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply