June 24, 2013 at 5:52 am
Hi,
Please find the following script.
Basically I want the tree structure to find the parent templates.
It should be 'N'number of output.
But current i can get only two set of output.
Before cursor one set of parent templates, after cursor second set of parent templates..
Actual work for the below script: giving input, it finds all parent template(before cursor) and am sending the result to cursor-its giving another set of parent list.
But again after cursor, what ever templates is displaying it can call from other templates.
So the tree structure keep on goes.
Please help me how can i do it dynamically or any other way to get the 'N'number of parent templates.
I can't go another cursor, its really bad way.
it should like
'tony>test1>test3>data
test>1>2>3>5>..................>>> .. keep on goes.
IF OBJECT_ID('helper_template') IS NOT NULL
BEGIN
DROP PROC kbmhelper_template
END
GO
CREATE PROCEDURE helper_template
(
@input VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
declare @table_name varchar(35)
declare @field_name varchar(35)
IF OBJECT_ID('tempdb..#Search_results') IS NOT NULL
BEGIN
DROP TABLE #Search_results
END
create table #Search_results
(
called_from varchar(35),
template_display_name Varchar(200) default '',
field VARCHAR(300),
field_name VARCHAR(200),
field_value VARCHAR(200),
template_type varchar(200) default '' ,
con_template_field varchar(2000) default ''
)
CREATE NONCLUSTERED INDEX IX_NON_RESULT
ON #search_results(called_from)
insert into #Search_results (called_from, field, field_name,field_value)
select distinct template_name, tr.field, '',@input
from test tr WITH(NOLOCK)
inner join templates t WITH(NOLOCK)
on tr.template_id = t.template_id
where tr.action in ('launch template', 'template pop-up')
and tr.parm1 = @input
and tr.parm1 not like ('{%')
and tr.parm1 not like ('@%')
UNION ALL
select distinct t.template_name,tr.field, xr.field_name,xr.field_value
from test tr WITH(NOLOCK)
inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50))
inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id
and tr.action = 'assign field value'
and '{'+xr.field_name+'}' in (
select parm1
from test
where action in ('launch template', 'template pop-up')
and parm1 like '{%'
and template_id = xr.template_id
)
and xr.field_value = '"'+@input+'"'
and xr.field_value not like '{%'
and xr.field_value <> '""'
UNION ALL
select distinct t.template_name,tr.field,xr.field_name,xr.field_value
from test tr WITH(NOLOCK)
inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50))
inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id
and tr.action = 'assign field value'
and xr.field_name in (
select parm1
from test WITH(NOLOCK)
where action in ('launch template', 'template pop-up')
and parm1 like '@%'
and template_id = xr.template_id
)
and xr.field_value = '"'+@input+'"'
and xr.field_value not like '{%'
and xr.field_value <> '""'
UNION ALL
select distinct table_name,'',tf.field_name,@input
from template_fields tf WITH(NOLOCK)
where table_name not like '%[_]'
and table_name <> '' and default_value <> ''
and default_value not like 'summary%'
and default_value not like '%Sheet%'
and field_type <> 'spgrd'
and default_value <> 'Family Unit'
and field_type <> 'relation'
and table_name not in ('date', 'data', 'data_in')
and field_name = @input
UNION ALL
select distinct t.template_name,tr.field,tm.field_name,tm.field_value
from test_1 tm
join test tr WITH(NOLOCK)
on tm.template_id = tr.template_id
join templates t
on tr.template_id = t.template_id
where tr.action in ('launch template','template pop-up')
and tm.field_name = Replace(REPLACE(tr.parm1,'{',''),'}','')
and REPLACE(tm.field_value,'"','') = @input
UPDATE st
set st.template_type = t.template_type,
st.template_display_name = t.template_display_name
FROM #Search_results ST
JOIN templates t
on st.called_from = t.template_name
UPDATE sr
set con_template_field = @input +' > '+ called_from +' (Field: '+sr.field+') (Template Type: '+sr.template_type+')'
FROM #Search_results sr
--select distinct called_from,template_display_name,field, field_name,field_value,template_type ,con_template_field
--from #Search_results WITH(NOLOCK)
--order by called_from
IF OBJECT_ID('tempdb..#final_results') IS NOT NULL
BEGIN
DROP TABLE #final_results
END
CREATE TABLE #Final_results
(
parent_name Varchar(100),
parent_field Varchar(300),
parent_type VARCHAR(100),
child_name VARCHAR(100),
child_field VARCHAR(200),
child_flow VARCHAR(500),
parent_flow VARCHAR(500)
)
CREATE NONCLUSTERED INDEX IX_NON_FINAL
ON #final_results(parent_name)
DECLARE @template_name VARCHAR(100)
DECLARE @child_field VARCHAR(200)
DECLARE Cur_results CURSOR FOR
SELECT called_from, field
FROM #Search_results
ORDER BY called_from
OPEN Cur_results
FETCH NEXT FROM Cur_results INTO @template_name, @child_field
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Final_results (parent_name)
select distinct template_name
from test tr WITH(NOLOCK)
inner join templates t
on tr.template_id = t.template_id
where tr.action in ('launch template', 'template pop-up')
and tr.parm1 = @template_name
and tr.parm1 not like ('{%')
and tr.parm1 not like ('@%')
UNION ALL
select distinct t.template_name
from test tr WITH(NOLOCK)
inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50))
inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id
and tr.action = 'assign field value'
and '{'+xr.field_name+'}' in (
select parm1
from test
where action in ('launch template', 'template pop-up')
--and parm1 like '{%'
AND PATINDEX('{%',parm1)>0
and template_id = xr.template_id
)
and xr.field_value = '"'+@template_name+'"'
and xr.field_value not like '{%'
and xr.field_value <> '""'
UNION ALL
select distinct t.template_name
from test tr WITH(NOLOCK)
inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50))
inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id
and tr.action = 'assign field value'
and xr.field_name in (
select parm1
from test WITH(NOLOCK)
where action in ('launch template', 'template pop-up')
--and parm1 like '@%'
and PATINDEX('@%',parm1)>0
and template_id = xr.template_id
)
and xr.field_value = '"'+@template_name+'"'
and xr.field_value not like '{%'
and xr.field_value <> '""'
UNION ALL
select distinct table_name
from template_fields tf WITH(NOLOCK)
where table_name not like '%[_]'
and table_name <> '' and default_value <> ''
and default_value not like 'summary%'
and default_value not like '%Sheet%'
and field_type <> 'spgrd'
and default_value <> 'Family Unit'
and field_type <> 'relation'
and table_name not in ('person', 'patient', 'person_encounter')
and field_name = @template_name
UNION ALL
select distinct t.template_name--,tr.field,tm.field_name,tm.field_value
from test_1 tm WITH(NOLOCK)
join test tr WITH(NOLOCK)
on tm.template_id = tr.template_id
join templates t
on tr.template_id = t.template_id
where tr.action in ('launch template','template pop-up')
and tm.field_name = Replace(REPLACE(tr.parm1,'{',''),'}','')
and REPLACE(field_value,'"','') = @template_name
UPDATE FS
SET FS.parent_field = TR.field,
FS.parent_type = T.template_type,
FS.child_name = @template_name
from #Final_results FS
join templates t
on FS.parent_name = t.template_name
join test tr
on t.template_id = tr.template_id
where tr.parm1 = @template_name
FETCH NEXT FROM Cur_results INTO @template_name, @child_field
END
CLOSE Cur_results
DEALLOCATE Cur_results
SELECT DISTINCT parent_name,
parent_field,
parent_type,
t.template_display_name as 'Parent_display_name',
child_name
FROM #Final_results fr WITH(NOLOCK)
JOIN templates t
on fr.parent_name = t.template_name
WHERE parent_field is not null
AND parent_name not in (select distinct called_From from #Search_results WITH(NOLOCK))
--@input +' > '+ called_from +' (Field: '+sr.field+') (Field Value: '+sr.field_value+') (Template Type: '+sr.template_type+')'
select distinct Case when sr.called_from = fr.child_name then sr.con_template_field +' > '+ fr.parent_name +' (Field: '+fr.parent_field+') (Template Type: '+fr.parent_type+')'
else sr.con_template_field end AS 'Template Workflow'
from #Search_results sr
left join #Final_results fr
on sr.called_from = fr.child_name
order by Case when sr.called_from = fr.child_name then sr.con_template_field +' > '+ fr.parent_name +' (Field: '+fr.parent_field+') (Template Type: '+fr.parent_type+')'
else sr.con_template_field end
SET NOCOUNT OFF
end
Thanks,
Tony
June 24, 2013 at 8:16 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
And why all the NOLOCK hints? Do you know all the dangers that hint brings to the table?
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2013 at 2:53 am
I am really sorry.
I couldn't provide data, its having more data and also i couldn't get it for specific data for sample.
please give me some idea how this can be achieved if we want to find tree structure from attached script.
Thanks,
tony
June 25, 2013 at 7:31 am
tonyarp05 61903 (6/25/2013)
I am really sorry.I couldn't provide data, its having more data and also i couldn't get it for specific data for sample.
please give me some idea how this can be achieved if we want to find tree structure from attached script.
Thanks,
tony
OK let me see if I understand. You can't post consumable ddl OR sample data but you want me to dig through over 300 lines of code referring to tables I don't know to come up with a solution to solve your problem. How exactly do you expect somebody to do that? If you want help you have to put in some effort. There is no chance that anybody is going to parse through what you posted and come up with an answer. There just aren't enough details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply