September 23, 2010 at 10:34 pm
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
September 23, 2010 at 10:38 pm
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 :
September 24, 2010 at 12:20 am
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.
September 24, 2010 at 12:28 am
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.
September 24, 2010 at 1:19 am
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...;-)
September 24, 2010 at 2:49 am
sathish.tbj (9/24/2010)
HiALTER 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
September 24, 2010 at 6:03 am
Hi Tommy,
The solution worked for me. You saved my day. Thanks a lot. 🙂
September 24, 2010 at 6:07 am
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.
September 24, 2010 at 6:25 am
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
September 24, 2010 at 8:48 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply