0

I have a strange behaviour using while in sybase. I have a temporary table tempdb..tmp_items with columns: uid, model, item. Then in my sql script I want to populate a #tmp_table with the item value and the relatives values taken from another table. To do that, I wrote a while loop. However, a final result in the tmp table I have only one lines and not all lines with all items. Are you able to help me with this? Thanks Pazzeo


CREATE TABLE #tmp_table (
items VARCHAR(12),
first_value VARCHAR(10) NULL,
last_value VARCHAR(10) NULL
)

GO

DECLARE @current_id int
SELECT @current_id = 0

DECLARE @nrows int
SELECT @nrows = 1

DECLARE @item_check VARCHAR(12)
DECLARE @first_value VARCHAR(10)
DECLARE @last_value VARCHAR(10)

WHILE @nrows > 0
BEGIN
   SET rowcount 1
   SELECT @item_check = item, @current_id = uid from tempdb..tmp_items where uid > @current_id
   SELECT @nrows = @@rowcount
    -- perform operation using @item_check
   SELECT TOP 1 @first_value = creation_date FROM t_movement WHERE (( state_code = 1 AND code = 3 ) AND ( item = @item_check ) ) ORDER BY creation_date ASC
   SELECT TOP 1 @last_value = creation_date FROM t_movement WHERE (( state_code = 1 AND code = 3 ) AND ( item = @item_check ) ) ORDER BY creation_date DESC
   INSERT INTO #tmp_table SELECT @item_check, @first_value, @last_value 
END
GO
user3303852
  • 1
  • 1
    Why are you setting `rowcount` - and have you remembered to `set rowcount 0`, otherwise when you select rows from your temp table in the same batch you will only get 1 row. – Stu Oct 03 '22 at 09:50

0 Answers0