Loop Thru a Table

Table definition please (pmddb..pmd_mpsp_ps_postn) and sample data.

I've done this with the help of CURSORS

Thanks to all .:-)

Good - you've gotten the business logic down. Now, if you'll post the table definitions, sample data, and the c.u.r.s.o.r. code, we can help you do it fast.

Thanks Wayne !!:-)

Actual Scenario

Hope that you are familiar with Production BOM (Manufacturing). Take an Example :

BPD (Variant 4000) is a Finished Item and it is assembled from the following Items (Assembly Item) BPD01(4000), BPD02(6000), BPD03(4000).

Relation between them : BOM No of the Finished Good Item BPD (Variant 4000) is same with Its Assembly Items BPD01(4000), BPD02(6000), BPD03(4000) and psp_io_flag is i of all the Assembly Items.

Every Assembly Items has its on BOM No where psp_io_flag is o and has no of Raw materials. Example : Assembly Items BPD01(4000) has Child items (Raw materials) BPDX01, BPDX02, Etc. BPD02(6000) has child items BPDX02, BPDX03 and so on.

Requirement : Output will be the all raw materials when someone gives Finished Goods as an input.

I attach the Table Definition as well as sql query for your kind information.

Code :

CREATE proc bom_details123

(

@item_no varchar(100),

@variant_no varchar(100)

)

as

DECLARE @BomNo INT

declare @quot int

declare @remd int

create table #temp

(

item_code varchar(50),

variant_code varchar(10),

bom_no int,

item_desc varchar(200),

uom varchar(10),

Quantity_mc numeric(28,3),

Available_Quantity numeric(28,3),

no_of_mechine numeric(28,3),

reminder numeric(28,3),

bom_item_code varchar(50),

bom_variant_code varchar(10),

bom_item_desc varchar(200),

bom_uom varchar(10),

)

SELECT @BomNo =psp_ps_no from pmddb..pmd_mpsp_ps_postn where psp_item_no = @item_no and psp_item_var =@variant_no

Declare @ItemCode varchar (100)

Declare @VarCode varchar (100)

Declare @BomNo2 varchar (100)

Declare @Cnt int

declare AssemblyList cursor for

SELECT psp_item_no, psp_item_var FROM pmddb..pmd_mpsp_ps_postn WHERE psp_ps_no =@BomNo and psp_item_no !=@item_no

OPEN AssemblyList

FETCH NEXT FROM AssemblyList

INTO @ItemCode,@VarCode

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @BomNo2 =psp_ps_no from pmddb..pmd_mpsp_ps_postn where psp_item_no = @ItemCode and psp_item_var =@VarCode and psp_io_flag = 'o'

SET @Cnt = (Select Count(*) From pmddb..pmd_mpsp_ps_postn WHERE psp_ps_no = @BomNo2)

-- PRINT @BomNo2

-- PRINT @Cnt

INSERT INTO #temp

(

item_code,variant_code,bom_no

)

SELECT psp_item_no,psp_item_var,psp_ps_no FROM pmddb..pmd_mpsp_ps_postn WHERE

psp_ps_no = @BomNo2 and psp_io_flag = 'i'

SET @BomNo2 =''

FETCH NEXT FROM AssemblyList

INTO @ItemCode,@VarCode

END

CLOSE AssemblyList

DEALLOCATE AssemblyList

Please, please, please, table definitions as a CREATE TABLE script. I can't copy-paste from a jpg. If you want help don't make the people offering their time do unnecessary work.

That was all explained in the article I referenced twice, I'm guessing you didn't bother reading it.

We still need sample data, some insert statements to populate the table.

USE [PMDDB]

GO

/****** Object: Table [dbo].[pmd_mpsp_ps_postn] Script Date: 06/01/2011 11:28:29 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[pmd_mpsp_ps_postn](

[psp_company_no] [dbo].[GLCOMPANY] NULL,

[psp_ps_no] [dbo].[UDD_PSNO] NOT NULL,

[psp_ps_rev_no] [dbo].[UDD_REVNO] NOT NULL,

[psp_ps_pos_no] [dbo].[UDD_POSNO] NOT NULL,

[psp_ps_pos_var_no] [dbo].[UDD_POSVARNO] NOT NULL,

[psp_desc] [dbo].[UDD_FULLDESC] NULL,

[psp_cond_no] [dbo].[UDD_CONDNO] NULL,

[psp_ref_designator] [dbo].[UDD_REFDESIG] NULL,

[psp_plng_grp_no] [dbo].[UDD_PLANGRPNO] NOT NULL,

[psp_item_no] [dbo].[IMSSTKNO] NULL,

[psp_item_var] [dbo].[IMSSTKVAR] NULL,

[psp_locn_no] [dbo].[GLLOCN] NULL,

[psp_wh_no] [dbo].[IMSWHNO] NULL,

[psp_io_flag] [dbo].[UDD_FLAG] NOT NULL,

[psp_ord_gen_flag] [dbo].[UDD_FLAG] NOT NULL,

[psp_qty_per_lot] [dbo].[cmn_Pqty] NULL,

[psp_qty_per_batch] [dbo].[cmn_Pqty] NULL,

[psp_qty_prpnl] [dbo].[cmn_Pqty] NULL,

[psp_tot_qty] [dbo].[cmn_Pqty] NULL,

[psp_prod_uom] [dbo].[IMSUOMNO] NULL,

[psp_scrap_perc] [dbo].[cmn_Plow] NULL,

[psp_multiplicator1] [dbo].[cmn_Pmed] NULL,

[psp_multiplicator2] [dbo].[cmn_Pmed] NULL,

[psp_ctrl_sum_flag] [dbo].[UDD_FLAG] NOT NULL,

[psp_pp_pos_no] [dbo].[UDD_SCHSLNO] NOT NULL,

[psp_lead_time_offset] [dbo].[cmn_Plow] NULL,

[psp_notes_no] [dbo].[UDD_NEWNOTESNO] NULL,

[psp_creatndt] [datetime] NOT NULL,

[psp_lstmoddt] [datetime] NULL,

[psp_lstusrid] [dbo].[cmn_userid] NULL,

[psp_timestamp] [timestamp] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Also, please confirm which version of SqlServer you are using.

This is a SqlServer 7 / 2000 forum, are you using a later release ?

You're using a significant number of user defined types. Please post the definitions of all of the ones in that table.

Still needs some sample data (as insert statements) please

SQL SERVER 2005

Plz see the attachment

Blood from a stone...

The user defined types?

Then you may find a recursive CTE performs better over large dataset