October 20, 2012 at 1:16 am
hi there, i need to generate results from two pivot query for that i use the global temporary table. but the situation is that when only one pivot query is executed there is no error but when both of the pivot query are used in the same sql the sql generated the error. the details are as follows:
DECLARE @Branch_List varchar(max)
DECLARE @PivotQuery varchar(max)
DECLARE @PivotQuery1 varchar(max)
DECLARE @tempTable varchar(100)
DECLARE @tempTable1 varchar(100)
Declare @TranDate date
set @TranDate='10/10/2012'
set @tempTable='##tempAll'
set @tempTable1='##tempOverdraft'
SELECT * into #temp1 FROM
(SELECT BranchCode,MainCode,Name,CyCode,
Bal=case when CyCode='01' then
case when MainCode='xxx' then (Balance-isnull(NegBalance,0)) /*For Current Account Deposit*/
else Balance end
else MiscBaln end
FROM FinancialTable
where MainCode like 'x%' and BranchCode<>'x' and TranDate='10/10/2012'/*@TranDate*/ and Balance<>0) as a
SELECT * into #tempOD FROM
(SELECT BranchCode,MainCode,Name,CyCode,Bal=NegBalance
FROM FinancialTable
where MainCode= 'xxx' and TranDate=@TranDate and Balance<>0 and BranchCode<>'x') as a
SELECT
@Branch_List =
STUFF
(
(
SELECT DISTINCT
', [' + BranchCode + ']'
FROM
BranchTable where BranchCode<>'x'
FOR XML PATH('')
), 1, 1, ''
)
set @PivotQuery=
N'SELECT
MainCode,CyCode,Name, ' + @Branch_List + ' into ' + @tempTable + '
FROM
(
select * from #temp1
)
as Data
PIVOT
(
SUM(Bal)
FOR
BranchCode
IN
(
' + @Branch_List + '
)
) PVT'
execute (@PivotQuery)
EXECUTE ( 'SELECT * into #tempFinal from ' + @tempTable )
set @PivotQuery1=
N'SELECT
MainCode,CyCode,Name, ' + @Branch_List + ' into ' + @tempTable1 + '
FROM
(
select * from #tempOD
)
as Data
PIVOT
(
SUM(Bal)
FOR
BranchCode
IN
(
' + @Branch_List + '
)
) PVT'
execute (@PivotQuery1)
EXECUTE ( 'SELECT * into #tempFinalOD from ' + @tempTable1 )
select * from #tempFinal
union
select * from #tempFinalOD
drop table #temp1
drop table #tempOD
drop table #tempFinal
drop table #tempFinalOD
drop table ##tempAll
drop table ##tempOverdraft
_____________________________________
i get the following error:
Msg 208, Level 16, State 0, Line 85
Invalid object name '#tempFinal'.
_________________________________________
please help me out anyone...
thanks in advance
October 20, 2012 at 3:07 am
i sort this out. thanks though.
besides now i am stuck in another dilemma. here it is :
i want to do this :
execute ('select * from ' + @tempFinal )
union
execute ('select * from ' + @tempFinalOD )
but the sql shows error in "union". how can get the result of these two "execute" statement in one report???
October 20, 2012 at 3:40 am
i again sort this out:
execute ('select * from ' + @tempFinal + ',' + @tempFinalOD )
October 20, 2012 at 6:57 am
Temporary table created through Execute statement by dynamic query cannot be accessed outside.
Here Table #tempFinal and #tempFinalOD is created by dynamic query.
To solve this issue first create these tables outside and then insert data and then use them.
Best of Luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply