Read table's next row

[antonopn]

This is my problem.
I have a table which is like this

"DK5" "184:00" "168:00" "00:00" 0 0 0 1
"F02" "185:00" "152:00" "00:00" 0 2 0 2
"SX1" "184:00" "107:00" "00:00" 3 0 0 3

I want to read every row until the end of the table.
Inside this I want another loop that reads next column's value.

there are no for loops as in C language. I can't find the appropriate words to "google it".

Thank you! :)

[deepuv04]

This is my problem.
I have a table which is like this

"DK5" "184:00" "168:00" "00:00" 0 0 0 1
"F02" "185:00" "152:00" "00:00" 0 2 0 2
"SX1" "184:00" "107:00" "00:00" 3 0 0 3

I want to read every row until the end of the table.
Inside this I want another loop that reads next column's value.

there are no for loops as in C language. I can't find the appropriate words to "google it".

Thank you! :)

if you want to loop through the records use cursor

[amitpatel66]

Are you trying in C or MSSQL?

[antonopn]

I use MS SQL.

If I use cursor how it will recognize the end of the rows?
@@fetch=0?

Thanks

[ck9663]

Some, including me, would recommend cursor as last resort. Would you mind telling us what your desired output is? It might be accomplished by a complex query.

-- CK

[antonopn]

OK lets see.

I want to read the first column which is an ID. After that real the content of the next column (lets say VAL1) . If it is not zero (<>0), I want to insert some values into a table. The values are VAL1, ID and some other ones which I can insert on my own.

After that read the next column etc.

Then go to the next row...

I also find it hard using cursors. Is it the only way?

[ck9663]

OK lets see.

I want to read the first column which is an ID. After that real the content of the next column (lets say VAL1) . If it is not zero (<>0), I want to insert some values into a table. The values are VAL1, ID and some other ones which I can insert on my own.

After that read the next column etc.

Then go to the next row...

I also find it hard using cursors. Is it the only way?

What are the other conditions and what do you want to do if those conditions are true. For now, I'll based this on just what you wrote.

You can try:

INSERT INTO YourDumpTable (VAL1, ID, SomeOtherColumn)
select Val1, ID, SomeOtherColumn from YourMainTable where ID <> 0

The above query will INSERT all records from YourMainTable into YourDumpTable all rows with ID <> 0

If you can list all your conditions, we might need to figure out some other ways. Am just basing the code on what you wrote.

-- CK

[antonopn]

I finally used cursor.

fetch next ... into  @myvariable1  @myvariable2  etc...

It works but I have another problem right now. Some codes are not correct (should not be taken into consideration). I should make a code that searches a table. If this code exists then insert into target-table values. This is to avoid duplicate records, because if the code does not exist a get a new row with the previous code. So I have the same code in two rows.

Well I'm just interested in finding out how NOT to use cursors..
Any "tutorial"?

THANK YOU ALL FOR YOUR PATIENCE!!!

[ck9663]

If you could post the table structures of your transaction table and your "dump" table and the conditions you want to perform before insert to "dump" table and your desired result (sample data), I think we can build a query for you.

-- CK

[antonopn]

OK. Here is the table.

"DK5" "184:00" "168:00" "00:00" "00:00" "00:00"
-------------------------------------------------------------------------
"F02" "185:00" "152:00" "01:00" "00:00" "00:00"
-------------------------------------------------------------------------
"SC1" "184:00" "107:00" "00:00" "05:00" "30:00"
-------------------------------------------------------------------------
"D2" "168:00" "64:00" "00:00" "00:00" "18:00"

Rows are defined by double quotes.

I want to read the first row. The first column is a code. I want to search another table (lets say it codes_table). If the code exists there then we read the second column. If it does not read the next row.

If it exists we read the next column. If it is >0 then insert into another table (lets say it Final_table) some values.

INSERT INTO FINAL_TABLE VALUES (DATE, CODE, VALUE_THAT_IS>0)

Then go to the next column and do the same thing. If it is also >0 a new row will be inserted with the same code. When it finishes all the columns, go to the next row. Here it will read a new code. It will search again at codes_table. If the code exists then etc...


If it is possible I want "date" to be defined by the final user! But except that getdate() function (also not defined by user) I can't find something...

Finally I want to be printed a message that would say how many rows were inserted in the Final_table with a DISTINCT code. For example 3 rows with code "DK5" count for one.

THANK YOU SO MUCH, CK!

[ck9663]

"Rows are defined by double quotes." I assume you mean Columns are defined by double quotes. I also convert your ":" to "." to make it a number.

One last question, what version of MSSQL are you using?

If you're using MS-SQL 2005, you're in luck. Paste this in a Query Window on your Management Studion

declare @t table
(code varchar(3), col1 decimal(6,2), 
 col2 decimal(6,2), col3 decimal(6,2),
 col4 decimal(6,2), col5 decimal(6,2))

declare @codes_table table (code varchar(3))

insert @codes_table values ('DK5')
insert @codes_table values ('F02')
insert @codes_table values ('D2')
insert @codes_table values ('XYZ')


insert @t values ('DK5', 184.00, 168.00, 00.00, 00.00, 00.00)
insert @t values ('F02', 185.00, 152.00, 01.00, 00.00, 00.00)
insert @t values ('SC1', 184.00, 107.00, 00.00, 05.00, 30.00)
insert @t values ('D2', 168.00, 64.00, 00.00, 00.00, 18.00)
insert @t values ('CK', 168.00, 64.00, 00.00, 00.00, 18.00)
insert @t values ('XYZ', 168.00, 64.00, 00.00, 00.00, 18.00)


select * from @t
select * from @codes_table

select code, codes, cols
from 
   (select code, col1, col2, col3, col4, col5 from @t t where t.code in (select cd.code from @codes_table cd)) p
unpivot
   (
      cols for Codes in 
         (col1, col2, col3, col4, col5)
   ) as unp
where cols > 0

You can put all these in a stored proc so that you can just pass the date as parameter.

-- CK