SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL function returning NULL value though the data is present.


SQL function returning NULL value though the data is present.

Author
Message
sathish.tbj
sathish.tbj
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 52
Hello,

I have created a SQl function that pulls up the concatenated text from the select query. The query has coalesce function embeded in it. The function always returns NULL when executed, though data is present. Help needed.

Function code:
==========
ALTER function [dbo].[GetEloquaColumn]
(@Code_String varchar(200)) returns varchar(300)
as
begin
declare @code varchar(200)
set @code =replace(@Code_String,',',''',''')
set @code=''''+@code+''''
DECLARE @CodeList varchar(250)
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (@code)

RETURN @CodeList
end
ColdCoffee
ColdCoffee
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36855 Visits: 5555
Sathish, without seeing your data, we can't debug the Function.. so can u cook up some sample data out of your original data and post it in the form of INSERT INTO <Table> VALUES () statement ?
Also please provide the DDLs of the source table.. if u find it difficult on how to do that, please read thro the following article :
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN
sathish.tbj
sathish.tbj
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 52
Hi

ALTER function [dbo].[GetEloquaColumn]
(@Code_String varchar(200)) returns varchar(300)

Here the value passed looks like [dbo].[GetEloquaColumn] ('HC01,HC02')

as
begin
declare @code varchar(200)
set @code =replace(@Code_String,',',''',''')
set @code=''''+@code+''''
DECLARE @CodeList varchar(250)
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (@code)

Here the VAlue should return like 'GONIT,GONIC' . This values stored in the table for the above passed codes.

RETURN @CodeList
end

When i execute the query directly by placing the values in the query it pulls the data. But it pulls NUll when executed as a function.
sathish.tbj
sathish.tbj
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 52
Function simplified further.

ALTER function [dbo].[GetEloquaColumn]
(@Code_String varchar(200)) returns varchar(300)
as
begin
DECLARE @CodeList varchar(250)
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (''''+ replace(@Code_String,',',''',''') +'''')

RETURN @CodeList
end

Structure of Slx_Code_refrence table
===========================
id, slx_pat_codes, Eloqua_Field_reference
1, HC01 , GON-IT
2, HC02 , GON-MI


So i ll be passing 'HC01,HC02' as a string and trying to pull out 'GON_IT,GON-MI' as a value.
ColdCoffee
ColdCoffee
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36855 Visits: 5555
How about this?

Function code:

IF OBJECT_ID (N'dbo.GetEloquaColumn', N'IF') IS NOT NULL
DROP FUNCTION dbo.GetEloquaColumn;
GO

CREATE FUNCTION [dbo].[GetEloquaColumn]
(
@Code_String NVARCHAR(200)
)
RETURNS TABLE
AS
RETURN
(
SELECT STUFF (Eloqua_Field_reference,1,1,'') Eloqua_Field_reference
FROM
(
SELECT ',' + Eloqua_Field_reference
FROM Slx_Code_refrence SCR
JOIN
(
SELECT
SUBSTRING(@Code_String,N,CHARINDEX(N',',@Code_String+N',',N)-N) AS ItemValue
FROM
dbo.Tally -- This is a numbers table ; Please visit this url
-- to know how it is built and what purpose it
-- serves
-- http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
WHERE
N BETWEEN 1 AND DATALENGTH(@Code_String)/2+1
AND SUBSTRING(N','+@Code_String,N,1) = N','
) SplitStrings (ItemValue)
ON
SplitStrings.ItemValue = SCR.slx_pat_codes
FOR XML PATH('')
) ElouqaTab (Eloqua_Field_reference)
);
GO



Test Harness:
IF OBJECT_ID('Slx_Code_refrence') IS NOT NULL
DROP TABLE Slx_Code_refrence
GO

CREATE TABLE Slx_Code_refrence
(
id INT IDENTITY(1,1) PRIMARY KEY,
slx_pat_codes VARCHAR(100),
Eloqua_Field_reference VARCHAR(100)
)
GO

INSERT INTO Slx_Code_refrence (slx_pat_codes, Eloqua_Field_reference)
SELECT 'HC01' , 'GON-IT'
UNION ALL SELECT 'HC02' , 'GON-MI'
UNION ALL SELECT 'HC03' , 'GON-03'
UNION ALL SELECT 'HC04' , 'GON-04'
UNION ALL SELECT 'HC05' , 'GON-05'
GO

SELECT * FROM dbo.[GetEloquaColumn] ('HC01,HC03')

IF OBJECT_ID('Slx_Code_refrence') IS NOT NULL
DROP TABLE Slx_Code_refrence



Please get back to us if you dont understand anything in the code...;-)
tommyh
tommyh
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5856 Visits: 2000
sathish.tbj (9/24/2010)
Hi

ALTER function [dbo].[GetEloquaColumn]
(@Code_String varchar(200)) returns varchar(300)

Here the value passed looks like [dbo].[GetEloquaColumn] ('HC01,HC02')

as
begin
declare @code varchar(200)
set @code =replace(@Code_String,',',''',''')
set @code=''''+@code+''''
DECLARE @CodeList varchar(250)
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (@code)

Here the VAlue should return like 'GONIT,GONIC' . This values stored in the table for the above passed codes.

RETURN @CodeList
end

When i execute the query directly by placing the values in the query it pulls the data. But it pulls NUll when executed as a function.


Thats because you think that the query that the function runs is
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (HC01,HC02)
but it isnt its
SELECT @CodeList=COALESCE(@CodeList+',','') + rtrim(ELOQUA_field_reference)
FROM sysdba.Slx_Eloqua_Code_Reference WHERE slx_pat_codes in ('HC01,HC02')
in otherwords SQL tries to find a SINGLE row that has the value 'HC01,HC02'
not two rows with values HC01 and HC02

Now if you actually would want that result something like this should work

Sometest code (for my benefit since i dont have the actual tables)

create table Slx_Eloqua_Code_Reference (ELOQUA_field_reference varchar(256), slx_pat_codes varchar(256))

insert into Slx_Eloqua_Code_Reference (ELOQUA_field_reference, slx_pat_codes) values ('GONIT', 'HC01')
insert into Slx_Eloqua_Code_Reference (ELOQUA_field_reference, slx_pat_codes) values ('GONIC', 'HC02')



The function (you will have to change it to alter instead and add the owner to the table)

create function [dbo].[GetEloquaColumn]
(@Code_String varchar(200)) returns varchar(300)
as
begin
declare @code varchar(200)
declare @Result TABLE (ItemNumber INT, ItemValue VARCHAR(8000))

DECLARE @XML XML;
SELECT @XML = '<r>'+REPLACE(@Code_String, ',', '</r><r>')+'</r>';

INSERT INTO @Result (ItemNumber, ItemValue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
Item.value('text()[1]', 'varchar(max)') AS ItemValue
FROM @XML.nodes('//r') R(Item);

DECLARE @CodeList varchar(250)
select @CodeList = ''

SELECT @CodeList = COALESCE(@CodeList,'') + rtrim(ELOQUA_field_reference) + ','
FROM Slx_Eloqua_Code_Reference WHERE slx_pat_codes in (select ItemValue from @Result)

if Len(@CodeList) > 0 select @CodeList = SubString(@CodeList, 1, Len(@CodeList) - 1)

RETURN @CodeList
end



Now
select dbo.GetEloquaColumn('HC01,HC02')


would result in 'GONIT,GONIC'


Now the code to parse the parameter i got from
http://www.sqlservercentral.com/Forums/Topic943562-338-3.aspx and might not be optimal. It was however the easiest to adapt to fit into your function.

/T
sathish.tbj
sathish.tbj
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 52
Hi Tommy,

The solution worked for me. You saved my day. Thanks a lot. :-)
sathish.tbj
sathish.tbj
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 52
Tommy,

I apologize for cross posting.

Can you please explain me about this particular area of a solution? I like to know the process.

--------------------------------------------------------------------------------------
DECLARE @XML XML;
SELECT @XML = '<r>'+REPLACE(@Code_String, ',', '</r><r>')+'</r>';

INSERT INTO @Result (ItemNumber, ItemValue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
Item.value('text()[1]', 'varchar(max)') AS ItemValue
FROM @XML.nodes('//r') R(Item);
--------------------------------------------------------------------------------------

Thanks in advance.
tommyh
tommyh
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5856 Visits: 2000
sathish.tbj (9/24/2010)
Tommy,

I apologize for cross posting.

Can you please explain me about this particular area of a solution? I like to know the process.

--------------------------------------------------------------------------------------
DECLARE @XML XML;
SELECT @XML = '<r>'+REPLACE(@Code_String, ',', '</r><r>')+'</r>';

INSERT INTO @Result (ItemNumber, ItemValue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
Item.value('text()[1]', 'varchar(max)') AS ItemValue
FROM @XML.nodes('//r') R(Item);
--------------------------------------------------------------------------------------

Thanks in advance.


Its code to parse a string into multiple values based on a separator. There is a huge discussion about this in the thread that i posted above.

This thread more precisly
http://www.sqlservercentral.com/Forums/Topic943562-338-3.aspx

I have to say that i dont exactly understand why the code looks the way it does. The thread is about the fastest way to parse. And fast doesnt necissarily mean readable. Personally i think i would have used an easier one with less performance. However i dont have one handy so i grabbed the first one that i could fit into your function without relying on external items.

/T
ColdCoffee
ColdCoffee
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36855 Visits: 5555
DECLARE @XML  XML;
SELECT @XML = '<r>'+REPLACE(@Code_String, ',', '</r><r>')+'</r>';


This
1. removes any comma ( , ) character in the input string
2. Replces </r><r> tags for the commas; the first </r> is for the closing tag of the value that lied before each comma and <r> is for the opening tag of the value that lied after each comma
3. appends <r> to the front and </r> to the end
4. Step 3 and 4 together will replace the commas and make a XML out of the comma-seperated string.
at the end of this step, your 'HC01,HC02' will look like <r>HCC01</r><r>HC02</r> which forms an XML.

INSERT INTO @Result (ItemNumber, ItemValue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
Item.value('text()[1]', 'varchar(max)') AS ItemValue
FROM @XML.nodes('//r') R(Item);


This is the real piece where the XML tags are parsed for the values between <r> and </r>
1. This - @XML.nodes('//r') - will identify the value betwent he tags (XNode is being used here)
2. R(Item) - Aliases the parsed value
3. Item.value('text()[1]' - This is where the parsed value is absorde and formed as a row
4. 'varchar(max)' - Cast the above step to VARCHAR(MAX)
At the end of this, you will have 2 rows, for HC01 and HC02

Hmmm, tommy showed the thread where people "fought" for the best parsing method.. In my function (which is above tommyh's function), i used the fastest string parser. and also mine is a inline-table valued funtion which is faster than scalar valued functions... Just try to run tommy's code and mine over a large data set and you will understand which is the fastest..

~Edit : Hyperlinked my function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search