| Author |
Topic |
|
Compkitty
Starting Member
24 Posts |
 Posted - 2005-05-23 : 09:19:40
|
I would like to create a procedure where it loops thru a table pulling out two fields. Taking both fields and casting them as a Datetime. After it casts, then it will do a DATEDIFF then fill in a column of another table. The issue I am having is w/ the Looping thru... I can't seem to get it to work right. Can someone help me w/ the syntax of how to do this? ...THANKS AHEAD OF TIME   |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-23 : 09:30:23
|
| ? Do you need to loop or could this be a single query.If you need to loop through each row you will need to use the primary key or a unique index or a temp table (or cursor).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 09:40:16
|
| I need to loop through.. how would I do it using a cursor? I thought I had that, but I guess I have it setup wrong.. So I trashed it to start over... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-23 : 09:48:42
|
| Knew I shouldn't have mentioned that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-05-23 : 09:56:03
|
| The best way to get an answer from here would be to explain exactly what you are trying to achieve, preferably with DDL for the CREATE TABLE statements for the tables that you are using and an example of the intended result.-------Moo. :) |
|
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 10:17:05
|
| Here is a simple one that I have come up w/...DECLARE @Col002 as varchar (10)DECLARE Col_002_Cursor CURSOR FORSELECT Col002 FROM CDR_Create ORDER BY Col002OPEN Col_002_CursorFETCH NEXT FROM Col_002_CursorINTO @Col002WHILE @@FETCH_STATUS = 0 BEGINPRINT @Col002ENDand if I am not mistaken shouldn't that pull out col002 and scroll thru while printing all the col002 values? Instead it's pulling out the first one and printing it as many times as there is rows in the table.. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-05-23 : 10:36:50
|
| But why are you wanting to do that?What's wrong with SELECT Col002 FROM CDR_Create ORDER BY Col002-------Moo. :) |
|
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 10:46:51
|
| Ok, well my reasoning for using (or trying to use) as cursor was so that it would loop thru and perform the calculations for me and add that column to a temp table... Can I do this w/ a Select? and then compute the calcs? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-05-23 : 11:09:15
|
| What calculations? -------Moo. :) |
|
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 11:17:12
|
| a DATEDIFF CalculationI want to put the answer into a column of another table.. |
|
|
|
vijayakumar_svk
Yak Posting Veteran
50 Posts |
Posted - 2005-05-23 : 11:28:30
|
quote:
Originally posted by Compkitty
Here is a simple one that I have come up w/...DECLARE @Col002 as varchar (10)DECLARE Col_002_Cursor CURSOR FORSELECT Col002 FROM CDR_Create ORDER BY Col002OPEN Col_002_CursorFETCH NEXT FROM Col_002_CursorINTO @Col002WHILE @@FETCH_STATUS = 0 BEGINPRINT @Col002ENDand if I am not mistaken shouldn't that pull out col002 and scroll thru while printing all the col002 values? Instead it's pulling out the first one and printing it as many times as there is rows in the table..
Put another Fetch statement before the "end" statement to show all the row values.. otherwise it will go into infinite loop.Work smarter not harder take control of your life be a super achiever |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-23 : 12:28:10
|
| no cursors, please !you can add as many calculations as you want to a standard SELECT. For example, if you have a table of Height and Width values, you can calculate the area for each row like this:select Height, Width, Height * Width as Areafrom yourtableAdd as many expressions as you like, separated by commas, in your SELECT list. As always, read up on SELECT in books on-line and I recommend a good book on beginning SQL to help you understand the thigns you can do with regular SELECT, INSERT, UPDATE and DELETE statments as opposed to using cursors.- Jeff |
|
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 14:06:08
|
| I wasn't able to use the calculations in my SELECT because the fields had to be formatted... So I was able to do it another then w/ the SELECT.. However, I now have would like to know how to add columns to an already built table and put this data into it... both tables have a field that relates. so once I can get the added column I would be awesome..I appreciate all the advise and help that you all have given |
|
|
|
Compkitty
Starting Member
24 Posts |
Posted - 2005-05-23 : 14:09:54
|
| Nevermind.. THANKS |
|
|
|
|
Looping Thru tables