Click here to Skip to main content
15,443,630 members

Comments by 7prince (Top 15 by date)

7prince 15-Mar-13 21:44pm View     CRLF
This error is pointing to 'AddLineItem' procedure that also EXECUTES GETDETAIL procedure. Currently got stuck in this error: Msg 515, Level 16, State 2, Procedure AddLineItem, Line 33 Cannot insert the value NULL into column table Salesdb.test.ORDERITEMS column does not allow nulls. INSERT fails. The statement has been terminated. /* -------------------------------------------------------------------------------- The TRANSACTION, this procedure calls GetNewDetail and performs an INSERT to the ORDERITEMS table which in turn performs an UPDATE to the INVENTORY table. Error handling determines COMMIT/ROLLBACK. -------------------------------------------------------------------------------- */ IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'AddLineItem') BEGIN DROP PROCEDURE AddLineItem; END; GO CREATE PROCEDURE AddLineItem --with orderid, partid and qty input @vOrderId VARCHAR(10), @vPartId SMALLINT, @vQty SMALLINT, @Stockqty SMALLINT, @vNewDetail SMALLINT OUTPUT AS DECLARE @vErr SMALLINT -- Error indicator DECLARE @vErrStr VARCHAR(80) -- Hold error message BEGIN TRANSACTION --- this is the only BEGIN TRANSACTION SET @vErr = 0 -- Check that requested Order No exists SELECT @vOrderid = Orderid FROM ORDERITEMS WHERE OrderID = @vOrderID --Call EXECUTE GetNewDetail @vOrderId, @vNewDetail OUTPUT; IF (@@ERROR <> 0 OR @@ROWCOUNT = 0) BEGIN SET @vErr = 1 SET @vErrStr = 'ERROR1 in AddlineItemSP: Order Number ' + @vOrderID + ' does not exist. Unable to complete order.' END; ELSE BEGIN -- Insert order record into OrderItems table INSERT INTO ORDERITEMS (orderid, partid, qty) VALUES (@vorderid,@vpartid,@vqty); --ERROR HANDLING IF (@@ERROR <> 0) BEGIN -- Error somewhere during the Insert SET @vErr = 2 SET @vErrStr = 'ERROR2 in AddlineItemSP: Unable to Insert Order No ' + @vOrderID END --END IF END; -- END IF -- The use of the system procedure RAISERROR allows identifying errors that occur elsewhere -- and doing any rollback/commit here, not in triggers IF (@vErr <> 0) BEGIN PRINT @vErrStr PRINT 'Order No ' + @vOrderid + ' for ' + CONVERT(VARCHAR(4),@vQty) + ' terminated with failure.' ROLLBACK TRANSACTION END; ELSE BEGIN PRINT @vErrStr PRINT 'Order No ' + @vOrderid + ' for ' + CONVERT(VARCHAR(4),@vQty) + ' completed successfully' COMMIT TRANSACTION END; -- END IF -- END TRANSACTION; --leave it commented out --END; GO -- No AddLineItem tests, saved for main block testing --EXECUTE AddLineItem 1007,1001,10,4 GO
7prince 9-Feb-13 20:09pm View    
the query gets the TopProfitable salesperson. so instead of using ROWNUM, How can I do the somEthing using RANK or DENSE_RANK ?
7prince 8-Feb-13 23:13pm View     CRLF
Here is the latest revision: But, I am still getting 0 rows updated message. Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid = (SELECT salespersons.empid FROM (SELECT * FROM (SELECT * FROM (SELECT s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary Topprofit FROM salespersons s LEFT JOIN orders o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid, s.ename, s.salary) ORDER BY Topprofit DESC) WHERE ROWNUM = 2 ORDER BY Topprofit ASC) WHERE ROWNUM = 1 and Topprofit is not null);
7prince 8-Feb-13 23:13pm View     CRLF
Here is the latest revision: But, I am still getting 0 rows updated message. Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid = (SELECT salespersons.empid FROM (SELECT * FROM (SELECT * FROM (SELECT s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary Topprofit FROM salespersons s LEFT JOIN orders o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid, s.ename, s.salary) ORDER BY Topprofit DESC) WHERE ROWNUM = 2 ORDER BY Topprofit ASC) WHERE ROWNUM = 1 and Topprofit is not null);
7prince 8-Feb-13 15:17pm View     CRLF
I ran the query and for some reason it say 0 row updated. Not sure why? Update salespersons set salespersons.salary = salespersons.salary * 1.12 where salespersons.empid = (SELECT salespersons.empid FROM (SELECT * FROM (SELECT * FROM (SELECT s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary Topprofit FROM salespersons s LEFT JOIN orders o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid, s.ename, s.salary) ORDER BY Topprofit DESC) WHERE ROWNUM = 2 ORDER BY Topprofit ASC) WHERE ROWNUM = 1 and Topprofit is not null);