Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Unanswered: GUYS please somenone help me out with this frustation Query...

    Guys please somenone help me out with this frustation Query...

    I have table looks like folowing
    Code:
    kode_product    formula     MaterialCode    Det_formula     Qty
    Snack           100         Choco           101             4
    Snack           100         Milk            102             3
    Snack           100         Seed                            7
    Snack           100         Sugar                           5
    Book            100         Paper                           4
    Book            100         String                          3
    Choco           101         Chocolate                       4
    Choco           101         Creamer                         2
    Choco           101         Colour                          3
    Milk            102         A-Milk          103             4
    Milk            102         Caramel                         5
    Pen             100         INK                             7
    Pen             100         plastic                         4
    A-Milk          103         Sugar                           4
    A-Milk          103         Gelatin                         2
    I want to make an output similiar like this
    Code:
    kode_product	formula		MaterialCode	Det_formula	Qty    
    Snack		100        	Choco        	101      	4    
    Choco	        101        	Chocolate          		4
    Choco		101        	Creamer          		2
    Choco		101        	Colour          		3
    Snack      	100      	Milk        	102      	3    
    Milk      	102      	A-Milk        	103      	4
    A-Milk        	103        	Sugar				4     
    A-Milk        	103        	Gelatin				2           
    Milk      	102      	Caramel				5
    Snack        	100        	Seed          			7    
    Snack        	100        	Sugar          			5    
    Pen        	101        	INK          			7    
    Pen        	102        	plastic          		4

    I have read about recursive programming but i still can't ....
    anyone?,,,,,or perhaps it's done with cursor programming? which is the step is
    1. select one row
    2. get the code_product and formula and save on variabel
    3. insert on temporary table
    4. select query where code_product and formula from variabel
    5. insert into temporary table
    6. get the 2nd row of cursor
    and so on....until last row.


    THIS QUERY ALMOST THERE...
    please improve this

    Code:
    DECLARE @RowsToProcess  int
    DECLARE @CurrentRow int
    DECLARE @SelectCol1 int
    DECLARE @Selkode_product nvarchar(50)
    DECLARE @SelFormula nvarchar(50)
    DECLARE @SelMaterialcode nvarchar(50)
    DECLARE @SelDet_Formula nvarchar(50)
    DECLARE @SelQty int
    
    
    DECLARE @test1 TABLE (RowID int not null primary key identity(1,1), [kode_product] nvarchar(50),[Formula] nvarchar(50),Materialcode nvarchar(50),Det_Formula nvarchar(50),Qty int)  
    INSERT into @test1 SELECT * FROM TEST
    SET @RowsToProcess=@@ROWCOUNT
    SET @CurrentRow=0
    WHILE @CurrentRow<@RowsToProcess
    BEGIN
        SET @CurrentRow=@CurrentRow+1
        SELECT 
            @Selkode_product=kode_product,
    		@SelFormula=Formula,
    		@SelMaterialcode=Materialcode,
    		@SelDet_Formula=Det_Formula,
    		@SelQty=Qty 
            FROM @test1
            WHERE RowID=@CurrentRow
    		
    	 INSERT INTO TEST1 Select @currentrow, [kode_product],[Formula],Materialcode,Det_Formula,Qty FROM @TEST1 WHERE RowID=@CurrentRow
         -- PRINT cast(@CurrentRow as char(5)) + ' ' + @Selkode_product + ' ' + @SelFormula + ' ' + @SelMaterialcode + ' ' + @SelDet_Formula + ' ' + cast(@SelQty as nchar(4))
    	 INSERT INTO TEST1 Select @currentrow,[kode_product],[Formula],Materialcode,Det_Formula,Qty FROM TEST WHERE KODE_Product = @SelMaterialCode AND Formula = @SelDet_Formula
    	 
      
    END
    NB : for information the parent formula is not always 100 =)
    Last edited by bny; 06-16-10 at 16:07.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    please could you wrap these sets up in code tags to preserve formatting? It is hard to read like that.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I see George has had a go. Do you see how this is starting to become easier to understand?

    However, it is still not right. Please can you line it up nicely? It is often easier to do this in a text editor using a fixed width font like courier, but do whatever is easier.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am not really sure what you are after here. Are you after a Bill of Materials?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Table one is done for you now. Please do table two in the same way but in black please.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bny View Post
    Nb. Dont mention about the colour and tab, that's for easy look for my code =)
    If you mean you can't be bothered changing the question to make it readable then I can't be bothered trying to figure out what your question is. Asking you to format the table is ultimately for your benefit.

  7. #7
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up

    Quote Originally Posted by pootle flump View Post
    Ok - I see George has had a go. Do you see how this is starting to become easier to understand? sorry, but yes it's like bill of material.

    However, it is still not right. Please can you line it up nicely? It is often easier to do this in a text editor using a fixed width font like courier, but do whatever is easier.
    thanks for attention...it's done pootle flump

    Quote Originally Posted by MCrowley View Post
    I am not really sure what you are after here. Are you after a Bill of Materials?
    Yes it's would you mind to help me out...

    Quote Originally Posted by pootle flump View Post
    Table one is done for you now. Please do table two in the same way but in black please.
    Thanks again and it's done....

    Quote Originally Posted by pootle flump View Post
    If you mean you can't be bothered changing the question to make it readable then I can't be bothered trying to figure out what your question is. Asking you to format the table is ultimately for your benefit.
    THanks it's done.... thanks again for your attention pootle flump

    Is there anyone help me out?...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you for fixing that.

    Now some questions:
    There is no quantity for A-Milk in table 2 - why is that?
    Code for Seed & Sugar has changed from 100 to ABC - why is that?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looks like you have fixed it. Am I right in thinking then that the only change here is the order of the data? I can't see any other differences.

  10. #10
    Join Date
    Feb 2009
    Posts
    17
    Quote Originally Posted by pootle flump View Post
    Thank you for fixing that.

    Now some questions:
    There is no quantity for A-Milk in table 2 - why is that?
    Code for Seed & Sugar has changed from 100 to ABC - why is that?
    My mistakes...thanks for informations...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I just posted another question right before your last post - please check it if you missed it!

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    That is all I see as well. No need for recursion, just a join to itself. Though, I am confused by paper and string disappearing in the desired result.
    Something like this should work, keep in mind, totally untested and just done in my head:
    Code:
    select kode_product
          ,formula
          ,MaterialCode
          ,Det_formula
          ,Qty    
       from  (select a.kode_product as parent_code
    		,coalesce(b.kode_product,a.kode_product)as kode_product
    		,coalesce(b.formula,a.formula)		as formula
    		,coalesce(b.MaterialCode,a.MaterialCode)as MaterialCode
    		,coalesce(b.Det_formula,a.Det_formula)	as Det_formula
    		,coalesce(b.Qty,a.Qty)			as Qty    
                  from ur_table a
               left outer join ur_table b
                  on b.formula <> 100
                 and b.kode_product = a.Materialcode
              where a.formula = 100) as c
    order by c.parent_code
            ,c.kode_product

  13. #13
    Join Date
    Feb 2009
    Posts
    17
    Quote Originally Posted by pootle flump View Post
    Looks like you have fixed it. Am I right in thinking then that the only change here is the order of the data? I can't see any other differences.
    Yes that's right.

    Quote Originally Posted by dav1mo View Post
    That is all I see as well. No need for recursion, just a join to itself. Though, I am confused by paper and string disappearing in the desired result.
    Something like this should work, keep in mind, totally untested and just done in my head:
    Code:
    select kode_product
          ,formula
          ,MaterialCode
          ,Det_formula
          ,Qty    
       from  (select a.kode_product as parent_code
    		,coalesce(b.kode_product,a.kode_product)as kode_product
    		,coalesce(b.formula,a.formula)		as formula
    		,coalesce(b.MaterialCode,a.MaterialCode)as MaterialCode
    		,coalesce(b.Det_formula,a.Det_formula)	as Det_formula
    		,coalesce(b.Qty,a.Qty)			as Qty    
                  from ur_table a
               left outer join ur_table b
                  on b.formula <> 100
                 and b.kode_product = a.Materialcode
              where a.formula = 100) as c
    order by c.parent_code
            ,c.kode_product
    Thanks for helping me, i appreciate....but the result of your query is not like my need, this is your results....any others suggestion dav1mo?
    Code:
    Book	 	100	Paper			4
    Book	 	100	String			3
    Pen	  	100	INK			7
    Pen	  	100	plastic		4
    Choco	101	Chocolate		4
    Choco	101	Creamer		2
    Choco	101	Colour		3
    Milk	 	102	A-Milk	103	4
    Milk	 	102	Caramel		5
    Snack	100	Seed			7
    Snack	100	Sugar			5
    Thanks again for attention dav1mo ,
    Last edited by bny; 06-16-10 at 16:13.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quickly thrown together to help others. I can't look at this now.
    Code:
    SELECT  * INTO order_tab
    FROM    (
    SELECT kode_product = 'Snack', formula = 100, MaterialCode = 'Choco', Det_formula = 101, Qty = 4
    UNION ALL SELECT 'Snack',            100         , 'Milk',              102,         3
    UNION ALL SELECT 'Snack',            100         , 'Seed', NULL,                            7
    UNION ALL SELECT 'Snack',            100         , 'Sugar', NULL,                           5
    UNION ALL SELECT 'Book',             100         , 'Paper', NULL,                          4
    UNION ALL SELECT 'Book',             100         , 'String', NULL,                          3
    UNION ALL SELECT 'Choco',            101         , 'Chocolate', NULL,                       4
    UNION ALL SELECT 'Choco',            101         , 'Creamer', NULL,                         2
    UNION ALL SELECT 'Choco',            101         , 'Colour', NULL,                          3
    UNION ALL SELECT 'Milk',             102         , 'A-Milk',            103,         4
    UNION ALL SELECT 'Milk',             102         , 'Caramel', NULL,                         5
    UNION ALL SELECT 'Pen',              100         , 'INK',          NULL,                    7
    UNION ALL SELECT 'Pen',              100         , 'plastic',           NULL,               4
    UNION ALL SELECT 'A-Milk',           103         , 'Sugar',                  NULL,          4
    UNION ALL SELECT 'A-Milk',           103         , 'Gelatin',                     NULL,     2) AS x

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what I gave you though looks like a step in the right direction for what you were after, You need to adjust it to get the exact results. You could try removing
    on b.formula <> 100
    see if that gets you any closer. From what I see in the results you posted you are not getting the parent row when there are children, so play around with it til you have it. I mean the forum is for help, advice and sharing new ideas, not to do your work for you.
    Dave

Posting Permissions

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