FETCH STATEMENT

[RJ5]

Hello again.

My fetch statement looks like this.


DECLARE r_cursor CURSOR
FOR SELECT * FROM rtemptable

OPEN r_cursor
FETCH NEXT FROM r_Cursor INTO @subdistcode,@itemcode,
@qtycases,@qtypcs,@amtcases,@amtpcs,@convfac

DECLARE @jj numeric, @jj1 numeric
WHILE @@FETCH_STATUS = 0
BEGIN
IF @qtypcs > @convfac
BEGIN
@JJ = DIV(@qtypcs / @convfac) -this would replace
the @qtycases
@JJ1 =MOD(@qtypcs /@convfac) -this would replace
the @qtypcs
INSERT rtemptable (col1,col2,col3,col4,
col5,col6,col7)
VALUES(@subdistcode,@itemcode,
@JJ,@JJ1,@amtcases,
@amtpcs,@convfac
FETCH NEXT FROM r_Cursor INTO
@subdistcode,@itemcode,
@qtycases,@qtypcs,@amtcases,
@amtpcs,@convfac
END
END

is this statement valid? Can I use values in the variables (@qtypcs and @convfac) in a condition(IF..ELSE) and replace the variables with the result of the condition (@JJ and @JJ1) and insert it in the same table (rtable) being used to fetch data?

many thanks for any enlightenment.

Randy : )

 

[tlbroadbent]

In general the syntax looks correct except for a missing right parentheses at the end of the Values list. I'm not familiar with the DIV and MOD functions in your SQL. I don't believe those functions exist in SQL Server. Are they User Defined Functions? What do they do?

Although what you have written will work, I recommend that you throw out the cursor, the fetches and inserts and use T-SQL power to update your table. You can do all that the posted SQL script does with one T-SQL statement. It will be much faster and more efficient.

In the sample that follows, I assume MOD is supposed to be the modulus operator and have substituted the SQL modulus operator "%". I left DIV in place because I can't come up with a substitute for it.

INSERT rtemptable
SELECT col1, col2, DIV(col3/col7), (col4 % col7), col5, col6, col7
FROM rtemptable
WHERE col4>col7 Terry

http://www.defendscouting.com/

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes

 

[RJ5]

Thanks again Terry.

MOD and DIV are functions that I use in VB6. I haven't tried them in SQL though.

: )