how to avoid this error in this select query which i have mentioned?
-
declare @table1 table ( catid int,categoryname varchar(100) )
insert into @table1
select 1,'cricket' union all
select 2,'football'
declare @table2 table ( sid int,catid int,subcatgoryname varchar(100))
insert into @table2
select 1,1,'bowling&fielding' union all
select 2,1,'batting' union all
select 3,1,'fielding' union all
select 4,2,'foul' union all
select 5,2,'goal'
declare @table3 table (adforumid int,memberid int,adforumname varchar(100),activesstatus bit)
insert into @table3
select 1,1,'inform',1
declare @table4 table(adinfid int,adforumid int,cid int,sid int)
insert into @table4
select 1,1,1,1 union all
select 2,1,1,2 union all
select 3,1,2,4
declare @table5 table(memberid int,membername varchar(100),firstname varchar(100),lastname varchar(100))
insert into @table5
select 1,'john','paul','stepen' union all
select 2,'andrew','sam','winson'
;with cte1 as(
select t4.adinfid, t5.membername, t5.memberid, t3.adforumname
, t1.catid, t1.categoryname, t2.sid, t2.subcatgoryname
from @table3 as t3
inner join @table5 as t5 on t3.memberid=t5.memberid
inner join @table4 as t4 on t3.adforumid=t4.adforumid
inner join @table1 as t1 on t4.cid=t1.catid
inner join @table2 as t2 on t4.cid=t2.catid and t2.sid = t4.sid
where t3.activesstatus=1
)
, cte2 as(
select memberid, membername, adforumname
, '[' + CAST(catid as varchar) + '|' + categoryname + ','+ stuff(
(select ', '+Cast(t2.sid as varchar(10)) +'| '+t2.subcatgoryname
From cte1 t2 where t2.memberid = t1.memberid and t1.catid = t2.catid for xml path('')),1,1,'') + ']' as name
from cte1 t1
group by memberid, membername, adforumname, catid, categoryname
)
select t1.memberid, t1.membername, t1.adforumname
, stuff((select ' ' + name
from cte2 t2 where t1.memberid=t2.memberid and t1.adforumname = t2.adforumname
for xml path('')
),1,1,'') as name
from cte2 t1
group by t1.memberid, t1.membername, t1.adforumname
ti is providing output like this when i execute this proc
[1|cricket, 1| bowling&fielding, 2| batting] [2|football, 4| foul]
but the out put should be like this
[1|cricket, 1| bowling&fielding, 2| batting] [2|football, 4| foul]
plz tell me how to avoid amp;amp;
-
sivajii (8/31/2012)
ti is providing output like this when i execute this proc[1|cricket, 1| bowling&fielding, 2| batting] [2|football, 4| foul]
but the out put should be like this
[1|cricket, 1| bowling&fielding, 2| batting] [2|football, 4| foul]
plz tell me how to avoid amp;amp;
The top few lines of this article tell you why this is happening. Here's a TSQL workaround:
;with cte1 as(select t4.adinfid, t5.membername, t5.memberid, t3.adforumname
, t1.catid, t1.categoryname, t2.sid, t2.subcatgoryname
from @table3 as t3
inner join @table5 as t5 on t3.memberid=t5.memberid
inner join @table4 as t4 on t3.adforumid=t4.adforumid
inner join @table1 as t1 on t4.cid=t1.catid
inner join @table2 as t2 on t4.cid=t2.catid and t2.sid = t4.sid
where t3.activesstatus=1
)
, cte2 as(
select memberid, membername, adforumname
, '[' + CAST(catid as varchar) + '|' + categoryname + ','+ stuff(
(select ', '+Cast(t2.sid as varchar(10)) +'| '+ REPLACE(t2.subcatgoryname,'&','¬')
From cte1 t2 where t2.memberid = t1.memberid and t1.catid = t2.catid for xml path('')),1,1,'') + ']' as name
from cte1 t1
group by memberid, membername, adforumname, catid, categoryname
)
select t1.memberid, t1.membername, t1.adforumname
,
REPLACE(
stuff((select ' ' + name
from cte2 t2 where t1.memberid=t2.memberid and t1.adforumname = t2.adforumname
for xml path('')
),1,1,'')
,'¬','&')
as name
from cte2 t1
group by t1.memberid, t1.membername, t1.adforumname
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Try this
;with cte1 as(select t4.adinfid, t5.membername, t5.memberid, t3.adforumname
, t1.catid, t1.categoryname, t2.sid, t2.subcatgoryname
from @table3 as t3
inner join @table5 as t5 on t3.memberid=t5.memberid
inner join @table4 as t4 on t3.adforumid=t4.adforumid
inner join @table1 as t1 on t4.cid=t1.catid
inner join @table2 as t2 on t4.cid=t2.catid and t2.sid = t4.sid
where t3.activesstatus=1
)
, cte2 as(
select memberid, membername, adforumname
, '[' + CAST(catid as varchar) + '|' + categoryname + ','+ stuff(
((select ', '+Cast(t2.sid as varchar(10)) +'| '+t2.subcatgoryname
From cte1 t2 where t2.memberid = t1.memberid and t1.catid = t2.catid for xml path(''),type).value('./text()[1]','varchar(max)') ) ,1,1,'') + ']' as name
from cte1 t1
group by memberid, membername, adforumname, catid, categoryname
)
select t1.memberid, t1.membername, t1.adforumname
, stuff(((select ' ' + name
from cte2 t2 where t1.memberid=t2.memberid and t1.adforumname = t2.adforumname
for xml path(''),type).value('./text()[1]','varchar(max)')
),1,1,'') as name
from cte2 t1
group by t1.memberid, t1.membername, t1.adforumname
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply