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  SQL Server 2000 Forums  SQL Server Development (2000)  Can cursors be avoided here

Author  Topic  nb Starting Member 9 Posts Posted - 2003-01-27 : 03:03:41 Hi everyone there, Going thru this site. I have learnt that people here abhore cursors. So do i. But I cant find a way out of this problem without cursors. Hope you guru’s can help me out. In my database I store in which bill of exchange items have arrived in BOE_Header and boe_Details . when ever I take a DO I have to pull the data on which Bill of exchange the corresponding item has arrived. It would have simple had it been the same quantity arrived and sold. I will give two scenarios. Item A arrives two times with quantities 20 (with BOE 100) and 30(103). I proceed to take a DO for the said item for quanity 25(it should follow FIFO). My DOBOE_Details should contain Item Qty BOENo A 20 100 A 5 103 Supposing again if I take a DO of item A for Qty 10 It should give me Item Qty BOENO A 10 103( i had mistakenly put 104 here before) BOE_header CREATE TABLE [dbo].[BOE_Header] ( [Billhkey] [int] IDENTITY (1, 1) NOT NULL , [BOENo] [int] NOT NULL , [trnDate] [datetime] NOT NULL , [ShippingAgent] [varchar] (50) NULL , [PortofLoading] [varchar] (50) NULL , [PortofDischarge] [varchar] (50) NULL , [Quantity] [int] NULL , [TypeofPacking] [varchar] (20) NULL , [HSCode] [varchar] (20) NULL , [Origin] [varchar] (50) NULL , ) ON [PRIMARY] BOE_Details CREATE TABLE [dbo].[BOE_Details] ( [Billdkey] [int] NULL , [BillHkey] [int] NULL , [loc_code] [char] (3) NULL , [Item_code] [varchar] (30) NULL , [Unit] [varchar] (10) NULL , [Weight] [decimal](18, 2) NULL , [Price] [decimal](18, 2) NULL , [Qty] [int] NULL , [Shp_qty] [int] NULL ) ON [PRIMARY] DO containts the Delivery order information CREATE TABLE [dbo].[do_header] ( [dohkey] [int] IDENTITY (1, 1) NOT NULL , [trnno] [int] NOT NULL , [date] [datetime] NOT NULL , [invhkey] [int] NOT NULL , [ref] [varchar] (30) NULL , [total] [decimal](16, 2) NULL , [posted] [varchar] (1) NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[do_details] ( [dodetkey] [int] IDENTITY (1, 1) NOT NULL , [dohkey] [int] NOT NULL , [loc_code] [varchar] (6) NOT NULL , [item_code] [varchar] (20) NOT NULL , [ut_code] [varchar] (6) NOT NULL , [qty] [int] NOT NULL , [rate] [decimal](10, 2) NOT NULL , [value] [decimal](16, 2) NOT NULL , [invdetkey] [int] NULL , [discount] [decimal](8, 2) NULL , ) ON [PRIMARY] DOBOE should contain details on which bill of Entries the items have gone CREATE TABLE [dbo].[DOBOE_Header] ( [Doboehkey] [int] IDENTITY (1, 1) NOT NULL , [InvoiceNo] [int] NOT NULL , [DONo] [int] NULL , [Type] [char] (10) NULL , [Qty] [decimal](18, 2) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[DOBOE_Details] ( [DOBOENo] [int] NOT NULL , [DOBoeDetkey] [int] IDENTITY (1, 1) NOT NULL , [Item_code] [varchar] (30) NOT NULL , [Qty] [decimal](18, 0) NULL , [BoeNo] [varchar] (30) NULL , [Dodetkey] [int] NULL ) ON [PRIMARY] Edited by - nb on 01/27/2003 10:05:16 Edited by - nb on 01/27/2003 23:38:48