March 16, 2011 at 8:28 am
I created a custom function and sometimes it returns a NULL value. In my WHERE clause I have....
where dbo.ci(c.chg_ref_num) is not null
It's still returns records where that field is NULL. It's as if the WHERE clause never existed. What am I doing wrong?
March 16, 2011 at 8:31 am
Can you post the function script?
-- Gianluca Sartori
March 16, 2011 at 8:33 am
Function is not working properly. Nothing else could explain this.
March 16, 2011 at 8:34 am
CREATE FUNCTION dbo.CI(@chg_ref_num varchar(10))
RETURNS VARCHAR(1000)
AS
BEGIN
declare @ci varchar(1000)
select @ci = coalesce(@ci + ', ', ' ') + cast(cor.resource_name as varchar(50))
from ca_owned_resource cor
inner join lrel l on substring(l.r_persid, 4, 60) = substring(sys.fn_sqlvarbasetostr(cor.own_resource_uuid), 3, 60)
inner join chg c on c.persid = l.l_persid
and c.chg_ref_num = @chg_ref_num
order by cor.resource_name
return @ci
end
March 16, 2011 at 8:37 am
Ninja's_RGR'us (3/16/2011)
Function is not working properly. Nothing else could explain this.
Function works properly as far as I know. I use this same function whenever i need to concatenate data into one field. The only issue i know is that when it does return data, it starts with a blank character.
March 16, 2011 at 8:37 am
is250sp (3/16/2011)
CREATE FUNCTION dbo.CI(@chg_ref_num varchar(10))RETURNS VARCHAR(1000)
AS
BEGIN
declare @ci varchar(1000)
select @ci = coalesce(@ci + ', ', ' ') + cast(cor.resource_name as varchar(50))
from ca_owned_resource cor
inner join lrel l on substring(l.r_persid, 4, 60) = substring(sys.fn_sqlvarbasetostr(cor.own_resource_uuid), 3, 60)
inner join chg c on c.persid = l.l_persid
and c.chg_ref_num = @chg_ref_num
order by cor.resource_name
return @ci
end
So what does your function actually returns for the not correctly filtered rows?
March 16, 2011 at 8:40 am
Ninja's_RGR'us (3/16/2011)
is250sp (3/16/2011)
CREATE FUNCTION dbo.CI(@chg_ref_num varchar(10))RETURNS VARCHAR(1000)
AS
BEGIN
declare @ci varchar(1000)
select @ci = coalesce(@ci + ', ', ' ') + cast(cor.resource_name as varchar(50))
from ca_owned_resource cor
inner join lrel l on substring(l.r_persid, 4, 60) = substring(sys.fn_sqlvarbasetostr(cor.own_resource_uuid), 3, 60)
inner join chg c on c.persid = l.l_persid
and c.chg_ref_num = @chg_ref_num
order by cor.resource_name
return @ci
end
So what does your function actually returns for the not correctly filtered rows?
Sorry I am not sure what you are asking but if there is data then it concatenates and put it into one field, otherwise, it returns a NULL value.
March 16, 2011 at 8:42 am
What I mean is find a row that is not correctly filtered. Get the input param from that row and run the function manually. That will show you the problem.
March 16, 2011 at 8:42 am
I just want to add that if i write it to a temp table and requery WHERE CI is NULL then it works, however, in this instance I do not want to use a temp table.
March 16, 2011 at 8:44 am
Concatenating that way is not safe. You'd better use XML for that:
CREATE FUNCTION dbo.CI(@chg_ref_num VARCHAR(10))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @ci VARCHAR(1000)
SET @ci = STUFF((
SELECT ', ' + CAST(cor.resource_name AS VARCHAR(50))
FROM ca_owned_resource cor
INNER JOIN lrel l
ON SUBSTRING(l.r_persid, 4, 60) = SUBSTRING(sys.fn_sqlvarbasetostr(cor.own_resource_uuid), 3, 60)
INNER JOIN chg c
ON c.persid = l.l_persid
AND c.chg_ref_num = @chg_ref_num
ORDER BY cor.resource_name
FOR XML PATH('')
), 1, 1, SPACE(0))
RETURN @ci
END
Also, as a general suggestion, scalar UDFs are slow, because their execution plan is not joined to the one of the calling query. Use ITVFs whenever possibile, instead.
However, in some way, you original function returns non-null data for the parameters you supplied: there's no other explanation.
-- Gianluca Sartori
March 16, 2011 at 8:52 am
is250sp (3/16/2011)
I just want to add that if i write it to a temp table and requery WHERE CI is NULL then it works, however, in this instance I do not want to use a temp table.
In sql server WHERE IS NULL WORKS. Period, end of story.
Based on that FACT, the only possible explaination is that something goes wrong in the function. I'd really focus my attention there.
Good luck with this.
March 16, 2011 at 9:05 am
Here is a sample output of the results. Like i said earlier I use this same script for other fields for a long time and haven't had any complaints yet. As far as I know it works fine. The ci field is the results from the custom function.
chg_ref_numci
CHG15581 atlevmhhn101, atlevmhhn102, ricevmhhn101, ricevmhhn102, ricevmhhn103, ricevmhhn104, ricwvctr102
CHG15582 atlvmhhn100, atlvmhhn101, ricevmhhn101, ricevmhhn102, ricevmhhn103, ricevmhhn104, ricwvctr102
CHG15598 atlevmhhn101, atlevmhhn102, atlnasa100, atlnasa101, atlwhbak100, ricevmhhn101, ricevmhhn102, ricevmhhn103, ricevmhhn104, ricnasa103, ricnasa104
CHG15599NULL
CHG15602 atlnasa100, atlnasa101, ricnasa103, ricnasa104
CHG15607 atlnasa100, ricnasa103
CHG15609 atlevmhhn101, atlevmhhn102, ricevmhhn101, ricevmhhn102, ricevmhhn104, ricvmhhn101
CHG15611 atlwsql100, ricwsqlhn100, ricwvcm100
CHG15613 ricwvctr102
CHG15614 ricwweb100
CHG15643NULL
March 16, 2011 at 9:11 am
Please do this, as suggested already by Ninja's_RGR'us:
What I mean is find a row that is not correctly filtered. Get the input param from that row and run the function manually. That will show you the problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 16, 2011 at 9:19 am
Ninja's_RGR'us (3/16/2011)
What I mean is find a row that is not correctly filtered. Get the input param from that row and run the function manually. That will show you the problem.
Excuse me for being such a noob but I am still not exactly sure what you meant by "not correctly filtered". Do you mean where @chg_ref_num is not a valid value? In other words, all of @chg_ref_num should be 'CHGxxxx'. So replace that value with something like 'ABC' and run the function manually?
March 16, 2011 at 9:27 am
Not quite.
You know that for some c.chg_ref_num, the filtering works as expected and for others it does not.
The suggestion is to find one that works and one that does not and then run the commands in the function explicitly, line by line, in Management Studio, comparing the results.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply