January 13, 2011 at 8:45 am
Hi i trying make this
-------
DECLARE @ROW_NUM INT
SET @ROW_NUM = 0
SELECT
PC_ID,
'Prestação de Contas Nº '+CONVERT(VARCHAR(10),PC_ID)+' Mês '+CONVERT(VARCHAR(10),PC_MES) as Texto,
(
SET @ROW_NUM = +1
SELECT @ROW_NUM as row_num
) as rownum
FROM PRESTACAOCONTAS
WHERE ED_ID='|tmp.ed_id|'
ORDER BY PC_MES, PC_ID
-------
when i put "SET @ROW_NUM = +1" , cause a error .
How could I add the value of this variable?
January 13, 2011 at 8:52 am
You can't put a SET inside a query.
Try SELECT @ROW_NUM = @ROW_NUM + 1 as row_num, but no guarantees that it will do what you want
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 10:32 am
I Had Already tried That Way and gave error, also changed the idea to generate the expected result would be that this
Ex:
PC_ID Texto row_num
33 Prestacao Contas nº 33 / Mês 1 1
35 Prestacao Contas nº 35 / Mês 1 2
36 Prestacao Contas nº 36 / Mês 1 3
Now i make this:
PC_ID Texto
33 Prestacao Contas nº 1 / Mês 1
35 Prestacao Contas nº 2 / Mês 1
36 Prestacao Contas nº 3 / Mês 1
37 Prestacao Contas nº 1 / Mês 2
38 Prestacao Contas nº 2 / Mês 2
39 Prestacao Contas nº 3 / Mês 2
NEW SCRIPT
declare @PC_ID int,@PC_MES int,@PC_MES_A int,@ROW_NUM int,@ED_ID int
SET @ED_ID = '|tmp.ed_id|'
declare @TABELA table ( id_pc integer,texto varchar(200) )
set @ROW_NUM = 0
declare
cur_pc
cursor for
select
pc_id,
pc_mes
from TblRE_Edital4_PrestacaoContas
where ed_id = @ED_ID
order by PC_MES, PC_ID
open cur_pc
fetch next from cur_pc into @PC_ID, @PC_MES
while @@FETCH_STATUS = 0
begin
if (@PC_MES_A != @PC_MES)
begin
set @ROW_NUM = 1
end
else
begin
set @ROW_NUM = @ROW_NUM+1
end
insert into @TABELA (id_pc, texto) values (@PC_ID,'Prestação de Contas Nº '+convert(varchar(2),@ROW_NUM)+' Mês '+CONVERT(VARCHAR(10),@PC_MES))
set @PC_MES_A = @PC_MES
fetch next from cur_pc into @PC_ID, @PC_MES
end
select id_pc as pc_id, texto from @TABELA
close cur_pc
deallocate cur_pc
January 13, 2011 at 10:36 am
Thanks for your help! I am new to the forum and sorry for bad english ... I am Brazilian
just to help in the explanation above,
Prestação de Contas = Accountability
Mês = Month
January 13, 2011 at 11:08 am
Why don't you just define id_pc as an identity column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply