July 13, 2015 at 12:06 pm
Hi,
I need one query help let me explain my issue
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
create table #taskxref(id bigint unique, TaskID bigint, DataElement varchar(500), Result varchar(1000))
insert into #taskxref values(1, 1, '<<Name>>', 'ABC')
insert into #taskxref values(2, 1, '<<Salary>>', '500')
insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')
insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')
insert into #taskxref values(3, 2, '<<Salary>>', '300')
-- select * from #taskxref
now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
(
select TaskID, DataElement, Result from #taskxref where TaskId = @TaskId
)
select TaskId, REPLACE(@Name, DataElement, ISNULL(Result,'')) TaskName
from data
)
drop table #task
drop table #taskxref
drop function fn_TaskResolver
Thanks
July 13, 2015 at 2:15 pm
Nope, sorry. I have absolutely no idea what you are trying to accomplish.
July 13, 2015 at 2:36 pm
You could use one update statement per "dataelement".
edit: as usual I could be completely wrong!
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
create table #taskxref(id bigint, TaskID bigint, DataElement varchar(500), Result varchar(1000))
insert into #taskxref values(1, 1, '<<Name>>', 'ABC')
insert into #taskxref values(2, 1, '<<Salary>>', '500')
insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')
insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')
insert into #taskxref values(3, 2, '<<Salary>>', '300')
-- select * from #taskxref
update #task
set [name] = replace([name],b.[dataelement],b.[result])
from #taskxref b
where #task.taskid = b.taskid
and b.dataelement = '<<Name>>'
update #task
set [name] = replace([name],b.[dataelement],b.[result])
from #taskxref b
where #task.taskid = b.taskid
and b.dataelement = '<<Company>>'
update #task
set [name] = replace([name],b.[dataelement],b.[result])
from #taskxref b
where #task.taskid = b.taskid
and b.dataelement = '<<Salary>>'
select * from #task
July 14, 2015 at 10:09 am
inayatkhan (7/13/2015)
Hi,I need one query help let me explain my issue
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
create table #taskxref(id bigint unique, TaskID bigint, DataElement varchar(500), Result varchar(1000))
insert into #taskxref values(1, 1, '<<Name>>', 'ABC')
insert into #taskxref values(2, 1, '<<Salary>>', '500')
insert into #taskxref values(3, 2, '<<Name>>', 'XYZ')
insert into #taskxref values(3, 2, '<<Company>>', 'Test Inc.')
insert into #taskxref values(3, 2, '<<Salary>>', '300')
-- select * from #taskxref
now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
(
select TaskID, DataElement, Result from #taskxref where TaskId = @TaskId
)
select TaskId, REPLACE(@Name, DataElement, ISNULL(Result,'')) TaskName
from data
)
drop table #task
drop table #taskxref
drop function fn_TaskResolver
Thanks
The problem, as I understand it, is that you want a function that's INLINE, to recursively REPLACE the text values found in the Name field with the replacement values supplied in a table. If only one replacement were necessary, this would be a piece of cake, but I don't think an INLINE function is going to be able to do it. I don't quite see how I could construct a recursive CTE that could do it. If the number of possible changes is high, then even if I do manage to figure it out, it's going to probably have rather bad performance. I'll take another pass at this after lunch....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 11:03 am
Lol, kind of as an exercise I did do a recursive function for this task. As you said, I'm not sure about how it will perform, and I have not made any attempt yet to tune it.
CREATE FUNCTION fn_TaskResolver (
@TaskId bigint,
@name varchar(2000)
)
RETURNS TABLE
AS
RETURN (
WITH cte_taskxrefs AS (
SELECT DataElement, Result, ROW_NUMBER() OVER(ORDER BY id) AS xref#
FROM taskxref
WHERE TaskId = @TaskId
),
cte_replace AS (
SELECT REPLACE(@name, DataElement, Result) Name, 1 AS replace#
FROM cte_taskxrefs
WHERE xref# = 1
UNION ALL
SELECT REPLACE(Name, DataElement, Result), replace# + 1
FROM cte_replace cr
INNER JOIN cte_taskxrefs ctx ON ctx.xref# = replace# + 1
)
SELECT Name
FROM cte_replace
WHERE
replace# = (SELECT MAX(xref#) FROM cte_taskxrefs)
)
GO --end of function
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2015 at 11:25 am
Will you really have that amount of data elements? You could simply code for each possible data element. It would be even better if you change the EAV design into a properly normalized table to maintain less rows.
July 14, 2015 at 11:58 am
Scott,
Nice work. Don't have any idea how it will perform, but here's a prettified and full set script for the entire setup, including dropping the created objects, so that others wishing to test can easily dispose of what gets created:
CREATE TABLE dbo.taskxref (
id bigint unique,
TaskID bigint,
DataElement varchar(500),
Result varchar(1000)
)
insert into dbo.taskxref values(1, 1, '<<Name>>', 'ABC')
insert into dbo.taskxref values(2, 1, '<<Salary>>', '500')
insert into dbo.taskxref values(3, 2, '<<Name>>', 'XYZ')
insert into dbo.taskxref values(4, 2, '<<Company>>', 'Test Inc.')
insert into dbo.taskxref values(5, 2, '<<Salary>>', '300')
select * from dbo.taskxref
GO
CREATE FUNCTION dbo.fn_TaskResolver (
@TaskId bigint,
@name varchar(2000)
)
RETURNS TABLE
AS
RETURN (
WITH cte_taskxrefs AS (
SELECT DataElement, Result, ROW_NUMBER() OVER(ORDER BY id) AS xref_num
FROM dbo.taskxref
WHERE TaskId = @TaskId
),
cte_replace AS (
SELECT REPLACE(@name, DataElement, Result) AS [Name], 1 AS replace_num
FROM cte_taskxrefs
WHERE xref_num = 1
UNION ALL
SELECT REPLACE([Name], DataElement, Result), replace_num + 1
FROM cte_replace cr
INNER JOIN cte_taskxrefs ctx
ON ctx.xref_num = replace_num + 1
)
SELECT [Name] AS Changed_Name
FROM cte_replace
WHERE replace_num = (SELECT MAX(xref_num) FROM cte_taskxrefs)
)
GO
DECLARE @task AS TABLE (
TaskId bigint unique,
Name varchar(2000)
)
insert into @task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into @task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
select * from @task
SELECT T.TaskId, X.Changed_Name
FROM @task AS T
CROSS APPLY dbo.fn_TaskResolver(T.TaskId, T.Name) AS X
DROP TABLE dbo.taskxref
GO
DROP FUNCTION dbo.fn_TaskResolver
GO
I'm attaching the estimated (EDIT: and actual) execution plans, derived after creating the xref table and the function.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 3:15 pm
Thanks I hope its work in my condition.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply