Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: Loop Thru a Table

    I have a Temporary Table which has no of records (Rows). Now I want to go these Temp Table thru row by row. Is it Possible in SQL ???

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Use a T-SQL cursor:
    SQL Server Cursor Examples
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2011
    Posts
    7
    Quote Originally Posted by aflorin27 View Post
    Use a T-SQL cursor:
    SQL Server Cursor Examples
    Thanks for your reply..
    Will It be done without using a cursor ???

    Kindly reply.

  4. #4
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Yes , depenning on what you are trying to achieve. What is it yoiu are trying to do to the other table?
    Could you display the DDL of the tables? and explain the prurpose of the statement

  5. #5
    Join Date
    May 2011
    Posts
    7
    My Code Given below..

    CREATE proc aaa
    (
    @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

    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 = @BomNo and psp_io_flag = 'i'

    ******************
    This query gives me the Output(attachment) when I Execute it (exec aaa 'BPD01','4000')

    But I want to Insert those records which are related to BPD01 (item code) 4000(variant) from the same table

    BPD01,4000 has no of child item in this table I want insert those record only. Then I Want to next record BPD02,4000 and so on.
    Attached Thumbnails Attached Thumbnails BOMOP.JPG  

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This query gives me the Output(attachment) when I Execute it (exec aaa 'BPD01','4000')
    That is hard to believe. The INSERT statement only fills the columns item_code, variant_code and bom_no, not the columns item_desc, uom, ...

    You could replace the two SQL scripts with one:
    Code:
    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_item_no = @item_no and 
    	psp_item_var =@variant_no AND
    	psp_io_flag = 'i'
    Why do you want to do the INSERTs row-by-row? You could use a set oriented (as opposed to a row oriented) INSERT that will insert all the rows at once. It's easier to write and it's a lot faster.
    Code:
    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_io_flag = 'i'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    May 2011
    Posts
    7
    Thanks Wim

    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
    Attached Thumbnails Attached Thumbnails TableDef.JPG  
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have only a few minutes spare time, I read only part of your post. Your problem seems to be a standard bill-of-materials. Do a Google search on "recursive SQL bill of materials".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    May 2011
    Posts
    7
    Yes.
    I've done this using cursors(SQL SERVER 2005). Is there any way to do the same ?
    BTW Thanks to you
    Last edited by subrata.bauri; 06-01-11 at 04:18.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you do not need cursors to navigate a hierarchy. If you are on 2005 or 2008, your best bet would be to use a CTE (Common Table Expression). They handle recursion very well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •