September 15, 2011 at 3:44 pm
Hello everybody,
I'm having problems with the following code, when I use two local variables in a query. (see mark **problem**)
First I create a table, then I insert rows and the I want to update same values of that rows.
The problem is in the query that should return a value that is going to be updated.
The actual code uses a local table to store the variable, but I allready tried to set the value directly using "set @qtt = (select.........)
The query, when executed independently, works fine.
If I remove the variables and use fixed values the code works well.
Can anyone help me?
Regards,
Miguel
Code Sample:
set language portuguese
set nocount on
--Variaveis
declare @processos numeric(6,0), --numero de processos
@dedata datetime,
@atedata datetime,
@dedata1 datetime,
@pross Char(250), --nome do processo
@pross1 Char(250), --nome do processo para nome de campo
@col char(50), -- nome da coluna
@num numeric(3,0), -- coluna numero
@table1 char(50), --nome da tabela
@tabnum numeric(3,0), --numero de tabelas
@maxitem numeric(10,0),-- numero de Items
@contador numeric(10,0), -- contador
@Query1 char(250), -- texto para query
@Query2 char(500), -- texto para query
@qtt numeric(10,4),--quantidade por processo
@item char(25) --referencia
drop table tmp_disp1 --apaga tabela temporaria
--variavel tabela interna
declare@prosstab table (rid numeric(3,0),processo char(50)) --para identificar os processos
declare@itemtab table (rid numeric(10,0),ref char(25), nome char(200), qtt numeric(10,2),
novo numeric(10,2), Usado numeric(10,2),Encomendado numeric(10,2), StockPotencial numeric (10,2),
Saldo numeric (10,2)) --para identificar os artigos
declare@qtttab table ( quant numeric(10,4))
--set @tabnum = (select COUNT(*) from sysobjects where name like 'tmp_disp%')
--set @table = 'tmp_disp'
--set @table = @table + CAST(@tabnum as CHAR(3))
--Exec sp_RENAME tmp_disp, @table
--Atribuição de variaveis
set @dedata = '20111001'
set @atedata = '20111001'
set @dedata1 = '20120202'
--set @pross = 'Dakar N13'
set @num = 1
set @tabnum = 1
--set @col = 'tmp_disp.col'+cast(@num as CHAR(3))
set @contador = 1
--conta processos activos
set @processos = (select COUNT(distinct t0.bifref) from bi as t0 where --t0.ref = 'me0048' and
t0.ndos = 79
and t0.fechada = 0 and (t0.dedata>=@dedata or t0.atedata>= @atedata ) and (t0.dedata<=@dedata1) )
--print @processos
if @processos <= 100
--/*
create table tmp_disp (ref char(25), nome char(200),Qtt numeric(10,2),Novo numeric(10,2), Usado numeric(10,2),
Encomendado numeric(10,2), Potencial numeric(10,2),Saldo numeric(10,2),
Col1 numeric(10,2),Col2 numeric(10,2),Col3 numeric(10,2),Col4 numeric(10,2),Col5 numeric(10,2),
Col6 numeric(10,2),Col7 numeric(10,2),Col8 numeric(10,2),Col9 numeric(10,2),Col10 numeric(10,2),
Col11 numeric(10,2),Col12 numeric(10,2),Col13 numeric(10,2),Col14 numeric(10,2),Col15 numeric(10,2),
Col16 numeric(10,2),Col17 numeric(10,2),Col18 numeric(10,2),Col19 numeric(10,2),Col20 numeric(10,2),
Col21 numeric(10,2),Col22 numeric(10,2),Col23 numeric(10,2),Col24 numeric(10,2),Col25 numeric(10,2),
Col26 numeric(10,2),Col27 numeric(10,2),Col28 numeric(10,2),Col29 numeric(10,2),Col30 numeric(10,2),
Col31 numeric(10,2),Col32 numeric(10,2),Col33 numeric(10,2),Col34 numeric(10,2),Col35 numeric(10,2),
Col36 numeric(10,2),Col37 numeric(10,2),Col38 numeric(10,2),Col39 numeric(10,2),Col40 numeric(10,2),
Col41 numeric(10,2),Col42 numeric(10,2),Col43 numeric(10,2),Col44 numeric(10,2),Col45 numeric(10,2),
Col46 numeric(10,2),Col47 numeric(10,2),Col48 numeric(10,2),Col49 numeric(10,2),Col50 numeric(10,2),
Col51 numeric(10,2),Col52 numeric(10,2),Col53 numeric(10,2),Col54 numeric(10,2),Col55 numeric(10,2),
Col56 numeric(10,2),Col57 numeric(10,2),Col58 numeric(10,2),Col59 numeric(10,2),Col60 numeric(10,2),
Col61 numeric(10,2),Col62 numeric(10,2),Col63 numeric(10,2),Col64 numeric(10,2),Col65 numeric(10,2),
Col66 numeric(10,2),Col67 numeric(10,2),Col68 numeric(10,2),Col69 numeric(10,2),Col70 numeric(10,2),
Col71 numeric(10,2),Col72 numeric(10,2),Col73 numeric(10,2),Col74 numeric(10,2),Col75 numeric(10,2),
Col76 numeric(10,2),Col77 numeric(10,2),Col78 numeric(10,2),Col79 numeric(10,2),Col80 numeric(10,2),
Col81 numeric(10,2),Col82 numeric(10,2),Col83 numeric(10,2),Col84 numeric(10,2),Col85 numeric(10,2),
Col86 numeric(10,2),Col87 numeric(10,2),Col88 numeric(10,2),Col89 numeric(10,2),Col90 numeric(10,2),
Col91 numeric(10,2),Col92 numeric(10,2),Col93 numeric(10,2),Col94 numeric(10,2),Col95 numeric(10,2),
Col96 numeric(10,2),Col97 numeric(10,2),Col98 numeric(10,2),Col99 numeric(10,2),Col100 numeric(10,2))
--*/
insert into @prosstab
select ROW_NUMBER() OVER (ORDER BY t0.bifref ) AS rowid,
t0.bifref from bi as t0 where --t0.ref = 'me0048' and
t0.ndos = 79
and t0.fechada = 0 and (t0.dedata>=@dedata or t0.atedata>= @atedata ) and (t0.dedata<=@dedata1)
group by t0.bifref
--select * from @prosstab
--select * from tmp_disp
set @tabnum = (select COUNT(*) from sysobjects where name like 'tmp_disp%')
--print @tabnum
set @table1 = 'tmp_disp'+CAST(@tabnum as CHAR(3))
Exec sp_RENAME 'tmp_disp',@table1
while @num <= @processos
begin
set @col = 'tmp_disp'+rtrim(CAST(@tabnum as CHAR(3)))+'.col'+cast(@num as CHAR(3))
set @pross = (select processo from @prosstab where rid = @num)
Exec sp_RENAME @col, @pross , 'COLUMN'
set @num = @num + 1
end
--select * from tmp_disp1
insert into @itemtab
select ROW_NUMBER() OVER (ORDER BY t0.ref ) AS rowid,
t0.ref,(select design from st (nolock) where ref = t0.ref),
SUM( qtt) as qtt,((select stock from st where ref = t0.ref)
-(select isnull(sum(stock),0) from sa (nolock)
where sa.ref=t0.ref and armazem in ('30'))) novo,
(select isnull(sum(stock),0) from sa (nolock) where sa.ref=st.ref and armazem in ('30')) usado,
isnull(((select sum(linhas.qtt) from bi as linhas (nolock) inner join bo (nolock)
on linhas.bostamp=bo.bostamp where bo.ndos=27 AND BO.FECHADA=0 and linhas.ref=st.ref)),0) encomendado,
((select stock from st where ref = t0.ref)
+isnull(((select sum(linhas.qtt) from bi as linhas (nolock) inner join bo (nolock) on linhas.bostamp=bo.bostamp
where bo.ndos=27 AND BO.FECHADA=0 and linhas.ref=st.ref)),0)) stockpotencial,
(((select stock from st where ref = t0.ref)
+isnull(((select sum(linhas.qtt) from bi as linhas (nolock) inner join bo (nolock) on linhas.bostamp=bo.bostamp
where bo.ndos=27 AND BO.FECHADA=0 and linhas.ref=st.ref)),0)))-sum(t0.qtt) saldo
from bi as t0 inner join
bo t1 on t0.bostamp = t1.bostamp
inner join st on t0.ref=st.ref where --t0.ref = 'me0048' and
t0.ndos = 79
and t0.fechada = 0 and (t0.dedata>=@dedata or t0.atedata>= @atedata ) and (t0.dedata<=@dedata1) --and t0.ref <>''
and ST.BLOQUEADO=0 AND (ST.DESIGN not like '%ANULADA%') and
t0.ref between 'me' and 'me99999999'
group by t0.ref, st.ref
--select * from @itemtab
set @maxitem = (select MAX(t5.rid) from @itemtab as t5)
insert into tmp_disp1
select ref,nome,qtt, novo, usado, encomendado,StockPotencial, Saldo,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from @itemtab
set @num = 1
while @contador <= @maxitem
begin
set @num = 1
while @num <= @processos
begin
set @item = (select ''''+rtrim(ref)+'''' from @itemtab where rid = @contador)
set @pross = (select '['+rtrim(processo)+']' from @prosstab where rid = @num)
set @pross1 = (select ''''+rtrim(processo)+'''' from @prosstab where rid = @num)
--set @query2 = '(select isnull(round(sum(qtt),4),0) from (bi (nolock) inner join bo (nolock) on bi.bostamp = bo.bostamp) inner join st (nolock) on bi.ref = st.ref where bi.ref ='+rtrim(@item)+' and (bi.dedata>=''20111001'' or atedata>= ''20111001'' ) and (dedata<=''20120202'') and bo.fechada=0 and bi.fechada=0 and bo.ndos = 79 and bo.fref = '+rtrim(@pross1)+')'
--print @query2
print @item
print @pross1
--set @tabnome = '@qtttab'
delete from @qtttab
****Problem****
insert into @qtttab
select isnull(round(sum(qtt),4),0) from (bi (nolock)
inner join bo (nolock) on bi.bostamp = bo.bostamp)
inner join st (nolock) on bi.ref = st.ref
where bi.ref = ltrim(rtrim(@item)) and (bi.dedata>='20111001' or atedata>= '20111001' ) and (dedata<='20120202')
and bo.fechada=0 and bi.fechada=0 and bo.ndos = 79 and bo.fref = ltrim(rtrim(@pross1))
--execute (@query2)
-- select * from @qtttab
set @qtt = (select quant from @qtttab)
--print 'select isnull(round(sum(qtt),4),0) from (bi (nolock) inner join bo (nolock) on bi.bostamp = bo.bostamp) inner join st (nolock) on bi.ref = st.ref where bi.ref ='+rtrim(@item)+' and (bi.dedata>=''20111001'' or atedata>= ''20111001'' ) and (dedata<=''20120202'') and bo.fechada=0 and bi.fechada=0 and bo.ndos = 79 and bo.fref = '+rtrim(@pross1)+''
print @qtt
****End Problem******
--print 'update tmp_disp1 set '+rtrim(@pross)+' = '+rtrim(cast(@qtt1 as varchar))+' where ref = '+rtrim(@item)+''
set @query1 = 'update '+RTRIM(@table1)+' set '+rtrim(@pross)+' = '+cast(@qtt as varchar) +' where ref = '+rtrim(@item)+''
--print @query1
execute (@query1)
set @num = @num + 1
end
set @contador = @contador + 1
print @contador
end
select * from tmp_disp1
September 16, 2011 at 6:39 am
What exactly is the problem? Is there any error message?
What is the output of select * from @qtttab in the "problem section"?
If there are more than one row in @qtttab you should use a TOP 1 ORDER BY to make sure you always get the same result.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply