July 23, 2014 at 11:16 am
Hi Everyone,
Can anyone help me on writing this code using a CTE or any other method which do not use a table varaible or a temp table as the users do not permissioms to run this code from the application because of thier roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
Begin
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2
FROM [Value Entry] as VE, [G_L - Item Ledger Relation] as GLILER
where VE.[Entry No_]=GLILER.[Value Entry No_]
update temp1 set temp1.[Gen_ Prod_ Posting Group] = temp2.[Gen_ Prod_ Posting Group]
from temp1, temp2 where temp1.[Entry No_] = temp2.[G_L Entry No_]
update temp1 set temp1.[Gen_ Prod_ Posting Group] = temp2.[Gen_ Prod_ Posting Group]
from temp1 , temp2
where temp1.[Document No_] = temp2.[Document No_]
select distinct VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group]
into temp3
FROM [Value Entry] as VE , [G_L Entry] as A
where VE.[Document No_] = A.[Document No_]
update temp1 set temp1.[Gen_ Prod_ Posting Group] = temp3.[Gen_ Prod_ Posting Group]
from temp1 , temp3
where temp1.[Document No_] = temp3.[Document No_]
update [G_L Entry] set [G_L Entry].[Gen_ Prod_ Posting Group] = temp1.[Gen_ Prod_ Posting Group]
from [G_L Entry],temp1
where [G_L Entry].[Entry No_] = temp1.[Entry No_]
drop table temp1
drop table temp2
DROP TABLE temp3
end
Regards
SR
July 23, 2014 at 3:51 pm
How can the users run scripts to UPDATE tables if they can't even use table variables or temp tables?
July 23, 2014 at 4:14 pm
First, you aren't using actual temporary tables but permanent tables as temporary tables.
Try this:
Begin
SELECT
A.[Entry No_],
A.[G_L Account No_],
A.[Gen_ Prod_ Posting Group],
A.[Document No_],
A.[Posting Date]
INTO
#temp1
FROM
[G_L Entry] as A ,
INNER JOIN [G_L Account] as B
ON (A.[G_L Account No_] = B.[No_]);
SELECT
VE.[Entry No_],
VE.[Document No_],
VE.[Posting Date],
VE.[Gen_ Prod_ Posting Group],
GLILER.[G_L Entry No_]
INTO
#temp2
FROM
[Value Entry] as VE
INNER JOIN [G_L - Item Ledger Relation] as GLILER
ON (VE.[Entry No_] = GLILER.[Value Entry No_]);
update t1 set
[Gen_ Prod_ Posting Group] = t2.[Gen_ Prod_ Posting Group]
from
#temp1 t1
inner join #temp2 t2
on (t1.[Entry No_] = t2.[G_L Entry No_]);
update t1 set
[Gen_ Prod_ Posting Group] = t2.[Gen_ Prod_ Posting Group]
from
#temp1
inner join #temp2 t2
on (t1.[Document No_] = t2.[Document No_]);
select distinct
VE.[Entry No_],
VE.[Document No_],
VE.[Posting Date],
VE.[Gen_ Prod_ Posting Group]
into
#temp3
FROM
[Value Entry] as VE
inner join [G_L Entry] as A
on (VE.[Document No_] = A.[Document No_]);
update t1 set
[Gen_ Prod_ Posting Group] = t3.[Gen_ Prod_ Posting Group]
from
#temp1 t1
inner join #temp3 t3
on (t1.[Document No_] = t3.[Document No_]);
update gle set
[Gen_ Prod_ Posting Group] = t1.[Gen_ Prod_ Posting Group]
from
[G_L Entry] gle
inner join #temp1 t1
on (gle.[Entry No_] = t1.[Entry No_]);
drop table #temp1
drop table #temp2
DROP TABLE #temp3
end
July 23, 2014 at 4:28 pm
Sorry Lynn,
I replaced the # while formatting the text to update in the Site, I am using #temp tables in the query.
July 23, 2014 at 5:02 pm
ksrikanth77 (7/23/2014)
Sorry Lynn,I replaced the # while formatting the text to update in the Site, I am using #temp tables in the query.
Well, you changed the entire meaning of the code by removing the # from your code.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply