CROSS APPLY and Invalid Object Name

Question

Post reply

CROSS APPLY and Invalid Object Name

petitpere

SSCrazy

Points: 2520
More actions
November 3, 2010 at 4:44 am

#242599

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Convert your scalar function to a table-valued function. From BOL:

The APPLY operator allows you to invoke a table-valued function...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 2

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"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SwePeso (11/3/2010)
You don't need a tvf here.

Quite true, but you might benefit from it a lot.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

petitpere SSCrazy Points: 2520 More actions · Answer 4

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.

DaveBoltman SSC Veteran Points: 203 More actions · Answer 5

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)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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())

----------------------------------------------------------------------

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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