USE FVTCSalesDB declare Totalunitamount money declare Gaddamount money Declare Q
ID: 3583086 • Letter: U
Question
USE FVTCSalesDB declare Totalunitamount money declare Gaddamount money Declare Qunithold int declare aloloop int DECLARE Jim Cursorout CURSOR FOR SELECT h. salesorderid FROM users schema Salesorderheader h OPEN Jim Cursorout FETCH NEXT FROM Jim Cursorout into Qunithold; set @Totalunitamount 0 set laoloop (a afetch status DECLARE Jim CursorIn CURSOR for select d. unitprice from users schema. salesorderdetail d where d .salesorderid Qunithold open Jim CursorIn; set @Total unitamount 0 FETCH NEXT FROM Jim Cursor In into Qaddamount WHILE @@FETCH STATUS 30 begin set Totalunitamount- Totalunitamount +laddamount FETCH NEXT FROM Jim Cursor In into @addamount; end print Order number convert (varchar,@unithold,1) Order Total convert (varchar, Totalunit amount, 1) CLOSE Jim Cursor In DEAL LOCATE Jim Cursor In WHILE @oloop 0 begin FETCH NEXT FROM Jim Cursorout into Qunithold; set Doloop- @@fetch statusExplanation / Answer
The above given code is calculating the sum of sale price of all units under a sale order and printing the value for each order.
Instead of nested Cursors, SQL provides an inbuilt function to sum values of columns in a table. Thus, the inner cursor = "Jim_CursorIn" is not required. A single cursor will suffice.
New Code:
USE FVTCSalesDB
declare @Totalunitamount money
declare @addamount money
declare @unithold int
declare @oloop int
DECLARE Jim_CursorOut CURSOR FOR SELECT h.salesorderid FROM users_schema.salesorderheader h
OPEN Jim_CursorOut;
FETCH NEXT FROM Jim_CursorOut into @unithold;
set @oloop = @@fetch_status
WHILE @oloop = 0
begin
--replacing the entire section of Jim_CursorIn with the below select statement
set @Totalunitamount = (SELECT SUM(d.unitprice) from users_schema.salesorderdetail d where d.salesorderid=@unithold);
print 'Order number ' + convert(varchar,@unithold,1) + ' Order Total='+convert(varchar,@Totalunitamount,1);
FETCH NEXT FROM Jim_CursorOut into @unithold;
set @oloop = @@fetch_status
end
CLOSE Jim_CursorOut;
DEALLOCATE Jim_CursorOut;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.