By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Read table's next row

antonopn
P: 42
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! :)
Mar 31 '08 #1
Share this Question
Share on Google+
10 Replies


deepuv04
Expert 100+
P: 227
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
Mar 31 '08 #2

amitpatel66
Expert 100+
P: 2,367
Are you trying in C or MSSQL?
Mar 31 '08 #3

antonopn
P: 42
I use MS SQL.

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

Thanks
Mar 31 '08 #4

ck9663
Expert 2.5K+
P: 2,878
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
Mar 31 '08 #5

antonopn
P: 42
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?
Mar 31 '08 #6

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO YourDumpTable (VAL1, ID, SomeOtherColumn)
  2. 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
Mar 31 '08 #7

antonopn
P: 42
I finally used cursor.

Expand|Select|Wrap|Line Numbers
  1. 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!!!
Apr 1 '08 #8

ck9663
Expert 2.5K+
P: 2,878
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
Apr 1 '08 #9

antonopn
P: 42
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.
Expand|Select|Wrap|Line Numbers
  1. 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!
Apr 1 '08 #10

ck9663
Expert 2.5K+
P: 2,878
"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

Expand|Select|Wrap|Line Numbers
  1. declare @t table
  2. (code varchar(3), col1 decimal(6,2), 
  3.  col2 decimal(6,2), col3 decimal(6,2),
  4.  col4 decimal(6,2), col5 decimal(6,2))
  5.  
  6. declare @codes_table table (code varchar(3))
  7.  
  8. insert @codes_table values ('DK5')
  9. insert @codes_table values ('F02')
  10. insert @codes_table values ('D2')
  11. insert @codes_table values ('XYZ')
  12.  
  13.  
  14. insert @t values ('DK5', 184.00, 168.00, 00.00, 00.00, 00.00)
  15. insert @t values ('F02', 185.00, 152.00, 01.00, 00.00, 00.00)
  16. insert @t values ('SC1', 184.00, 107.00, 00.00, 05.00, 30.00)
  17. insert @t values ('D2', 168.00, 64.00, 00.00, 00.00, 18.00)
  18. insert @t values ('CK', 168.00, 64.00, 00.00, 00.00, 18.00)
  19. insert @t values ('XYZ', 168.00, 64.00, 00.00, 00.00, 18.00)
  20.  
  21.  
  22. select * from @t
  23. select * from @codes_table
  24.  
  25. select code, codes, cols
  26. from 
  27.    (select code, col1, col2, col3, col4, col5 from @t t where t.code in (select cd.code from @codes_table cd)) p
  28. unpivot
  29.    (
  30.       cols for Codes in 
  31.          (col1, col2, col3, col4, col5)
  32.    ) as unp
  33. where cols > 0
  34.  
  35.  
You can put all these in a stored proc so that you can just pass the date as parameter.

-- CK
Apr 1 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.