need Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need Cursor

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 03:51:25

Currentlotorderqty,Orginallotquantity are there in InventorySerialNumbers
Both Currentlotorderqty,Orginallotquantity will be equal initially

AvailableQty,AllottedQty field are there in TrnDetGreySourcePlan

This AvailableQty field = Currentlotorderqty
If we allot in frontend that allotedqty will be inserted in TrnDetGreySourcePlan and that quantity will be deducted in currentlotquantity(up to here i had done)

Now comming to deletion i have to add the alloted qty to currentlotqty in InventorySerialNumbers

FOR EX :If i execute this i will get 5 records
These are the
select td.*,isn.* from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber

Here as iam having multiple rows for one orderno:
when that orderno is deleted each and every row should be updated

just to get brief idea....the sp is:

DECLARE @AllottedQty int
SELECT @AllottedQty =AllottedQty FROM TrnDetGreySourcePlan
where CompanyID= @CompanyID
and DivisionID=@DivisionID
and DepartmentID=@DepartmentID
AND ORDERNo =@OrderNumber

DECLARE @Currentlotorderqty int
select @Currentlotorderqty = Currentlotorderqty + @AllottedQty from InventorySerialNumbers

Update InventorySerialNumbers
SET
Currentlotorderqty= @Currentlotorderqty
WHERE
CompanyID=@CompanyID
AND DivisionID=@DivisionID
AND DepartmentID=@DepartmentID
AND ORDERNo =@OrderNumber
i have to update each and every row of that order ...

The table structure
Initially after inserting

Batchno,available,alloted,orginallotorderqty,currentlotorderqty, orderno

2 100 30 100 70 1001
3 100 10 100 90 1001
4 100 20 100 80 1001

after deleting i need the sp for which only orderno passed
Batchno,available,alloted,orginallotorderqty,currentlotorderqty, orderno

2 100 0 100 70+30 1001
3 100 0 100 90+10 1001
4 100 0 100 80+20 1001

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:00:47

May be this:-

Update i
set i.currentlotorderqty=i.currentlotorderqty+i.available,
 i.available=0
FROM InventorySerialNumbers i
inner join (select td.*,isn.* from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where 
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
)t
on t.OrderNumber=i.ORDERNo

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 05:03:19

Only Batchno PRIMARYKEY HERE ,NOT ORDERNO.

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 05:11:59

error:The column 'CompanyID' was specified multiple times for 't'.
Hi visakha...
when i had executed
Update i

set i.currentlotorderqty=i.currentlotorderqty+i.available,
i.available=0
FROM InventorySerialNumbers i

inner join (select td.*,isn.* from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
)t
on t.Baseorderno=i.ORDERNUMBER

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 05:15:48

The available field is in TrnDetGreySourcePlan but here it was taken InventorySerialNumbers

set i.currentlotorderqty=i.currentlotorderqty+i.available,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:25:45

quote:
Originally posted by Anushka

The available field is in TrnDetGreySourcePlan but here it was taken InventorySerialNumbers

set i.currentlotorderqty=i.currentlotorderqty+i.available,

then change it accordingly also instead of using * use actual column names inside the subquery

Update i

set i.currentlotorderqty=i.currentlotorderqty+t.available,
i.available=0
FROM InventorySerialNumbers i

inner join (select td.*,isn.* from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where 
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
)t
on t.Baseorderno=i.ORDERNUMBER

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 05:35:30

it is showing The column 'CompanyID' was specified multiple times for 't'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:45:23

quote:
Originally posted by Anushka

it is showing The column 'CompanyID' was specified multiple times for 't'.

did you replace * with the columns you wanted? i had specifically told you to do that in last post. this error is because you have Company ID coming from both the tables. I didnt replace them as i dont know your column names.

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 06:01:45

same error :(
i wrote

Update i

set i.currentlotorderqty=i.currentlotorderqty+t.allottedQty
FROM InventorySerialNumbers i

inner join (select isn.PurchaseOrderNumber,isn.currentlotorderqty,isn.SerialNumber,td.allottedqty,td.companyid,td.divisionid,td.departmentid,td.BaseLineno,td.BaseOrderno,td.[FPS/TFSno],td.Batchno,isn.companyid,isn.divisionid,isn.departmentid,isn.OrderLineNumber,isn.OrderNumber from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
)t
on t.ORDERNUMBER=i.ORDERNUMBER

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 06:05:37

The currentlotorderqty in InventorySerialNumbers should be add up to allottedQty which is there in TrnDetGreySourcePlan
even after writing column names:it is showing The column 'CompanyID' was specified multiple times for 't'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 06:28:59

quote:
Originally posted by Anushka

The currentlotorderqty in InventorySerialNumbers should be add up to allottedQty which is there in TrnDetGreySourcePlan
even after writing column names:it is showing The column 'CompanyID' was specified multiple times for 't'.

you have it specified mulitple times! please remove one of them

Update i

set i.currentlotorderqty=i.currentlotorderqty+t.allottedQty
FROM InventorySerialNumbers i

inner join (select isn.PurchaseOrderNumber,isn.currentlotorderqty,isn.SerialNumber,td.allottedqty,td.companyid,td.divisionid,td.departmentid,td.BaseLineno,td.BaseOrderno,td.[FPS/TFSno],td.Batchno,isn.companyid,isn.divisionid,isn.departmentid,isn.OrderLineNumber,isn.OrderNumber from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where 
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
)t
on t.ORDERNUMBER=i.ORDERNUMBER

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-20 : 11:40:20

Hi,
Thanx for ur reply some extent iam getting by writing like this but ............
hi actually serial number brings the records which were alloted ,if not all the records will be updated for which the qty is not alloted but having orderno (passing parameter)
actually i.serialnumber = t.SerialNumber(which is the only primary key...
I think cursor is req to impliment this......
as only orderno iam passing from front end

ALTER PROCEDURE [dbo].[UpdateSerialNumber] --'pangea','default','default','2010'
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@OrderNumber NVARCHAR(36)

AS
BEGIN

Update i

set i.currentlotorderqty=i.currentlotorderqty+t.allottedQty
FROM InventorySerialNumbers i

inner join (select isn.PurchaseOrderNumber,isn.originallotorderqty ,isn.currentlotorderqty,isn.SerialNumber,td.allottedqty,td.BaseLineno,td.BaseOrderno,td.[FPS/TFSno],td.Batchno,isn.OrderLineNumber,isn.OrderNumber from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
and isn.currentlotorderqty ! = isn.originallotorderqty
and td.orderno =@OrderNumber
)t
on t.ORDERNUMBER=i.ORDERNUMBER

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 12:49:20

quote:
Originally posted by Anushka

Hi,
Thanx for ur reply some extent iam getting by writing like this but ............
hi actually serial number brings the records which were alloted ,if not all the records will be updated for which the qty is not alloted but having orderno (passing parameter)
actually i.serialnumber = t.SerialNumber(which is the only primary key...
I think cursor is req to impliment this......
as only orderno iam passing from front end

ALTER PROCEDURE [dbo].[UpdateSerialNumber] --'pangea','default','default','2010'
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@OrderNumber NVARCHAR(36)

AS
BEGIN

Update i

set i.currentlotorderqty=i.currentlotorderqty+t.allottedQty
FROM InventorySerialNumbers i

inner join (select isn.PurchaseOrderNumber,isn.originallotorderqty ,isn.currentlotorderqty,isn.SerialNumber,td.allottedqty,td.BaseLineno,td.BaseOrderno,td.[FPS/TFSno],td.Batchno,isn.OrderLineNumber,isn.OrderNumber from TrnDetGreySourcePlan td,InventorySerialNumbers isn
where
td.companyid=isn.companyid
and td.divisionid =isn.divisionid
and td.departmentid=isn.departmentid
and td.BaseLineno= isn.OrderLineNumber
and td.BaseOrderno=isn.OrderNumber
and td.[FPS/TFSno]=isn.PurchaseOrderNumber
and td.Batchno = isn.SerialNumber
and isn.currentlotorderqty ! = isn.originallotorderqty
and td.orderno =@OrderNumber
)t
on t.ORDERNUMBER=i.ORDERNUMBER

END

Dont you have serialno in any other table by which you can link to this table to perform the update?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources