Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Convert VBA to SQL Stored Procedure to Loop thru Records

    Our current Access Database solution needs to be moved over to SQL, we are running SQL 2000. There is a particular VBA function that is used in this Access database to look at a particular table which contains some data related to work orders & inventory requirements.

    The function loops thru the data, and reduces the inventory based on the qty required. When it gets to a new item #, it starts over again. Below is the code.

    Public Function CalcAGE1()

    Dim rst As DAO.Recordset, db As DAO.Database
    Dim SQLQuery As String
    Dim qty_oh As Double 'asoh
    Dim qty_r As Double 'total_qty
    Dim new_qoh As Double 'new_usoh
    Dim new_qoh_prev As Double 'new_usoh_prev
    Dim item_prev As String 'Item_No_Prev
    Dim item As String 'Item_No
    Dim ord_no As String 'Ord_No
    Dim status As String


    Set db = CurrentDb
    DBEngine.SetOption dbMaxLocksPerFile, 200000
    SQLQuery = "SELECT tbl_order_age.ord_no, tbl_order_age.item, tbl_order_age.qty_oh, tbl_order_age.qty_r, tbl_order_age.new_qoh, tbl_order_age.status, tbl_order_age.ord_no, tbl_order_age.start_date FROM tbl_order_age order by tbl_order_age.item, tbl_order_age.status, tbl_order_age.start_date,tbl_order_age.ord_no;"

    Set rst = db.OpenRecordset(SQLQuery)
    rst.MoveFirst

    item_prev = ""
    new_qoh_prev = 0

    Do Until rst.EOF
    qty_oh = rst!qty_oh
    qty_r = rst!qty_r
    item = rst!item
    If item_prev = item Then
    new_qoh = new_qoh_prev - qty_r
    Else
    new_qoh = qty_oh - qty_r
    End If


    rst.Edit
    rst!new_qoh = new_qoh

    rst.Update
    item_prev = rst!item
    new_qoh_prev = rst!new_qoh
    rst.MoveNext
    Loop

    End Function

    Attached example is result. The Yellow - Top portion is the result of the data being written. The Purple - Bottom, is the result after the function is run. I have not been successful moving this into SQL due to inexperience, so any help or pointing at a good example would be great.
    Attached Thumbnails Attached Thumbnails example.jpg  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    With cte
    As 
    (-- anchor data: those with ord_no = 1, I assume ord_no counts from 1 to n per item
    Select ord_no, 
    	item, 
    	status, 
    	start_date, 
    	qty_oh, 
    	qty_r, 
    	qty_oh - qty_r as new_qoh
    FROM #tbl_order_age
    WHERE #tbl_order_age.ord_no = 1
    	UNION ALL
    -- Recursive processing starts here
    Select T1.ord_no, 
    	T1.item, 
    	T1.status, 
    	T1.start_date, 
    	T1.qty_oh, 
    	T1.qty_r, 
    	CTE.new_qoh - T1.qty_r as new_qoh
    FROM #tbl_order_age as T1
    	INNER JOIN CTE ON
    		T1.item = cte.item AND
    		T1.ord_no = cte.ord_no + 1
    )
    UPDATE U
    SET U.new_qoh = cte.new_qoh
    from #tbl_order_age as U
    	INNER JOIN cte ON
    		U.item = cte.item AND
    		U.ord_no = cte.ord_no
    When I posted this answer, I noticed that you still use SQL Server 2000. 2000 doesn't understand recursion, so this solution won't be of any use to you.
    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

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work on SQL 2000: (poor man's recursive SQL Server 2000 solution)
    Code:
    CREATE TABLE #CTE(
    	ord_no	INT	NOT NULL, 
    	item	VARCHAR(10)	NOT NULL, 
    	status	CHAR(1)	NOT NULL, 
    	start_date DATE	NOT NULL,
    	qty_oh	INT	NOT NULL, 
    	qty_r	INT	NOT NULL, 
    	new_qoh	INT	NOT NULL
    )
    
    DECLARE @ord_no BIGINT
    SET @ord_no = 1
    
    INSERT INTO #CTE
    Select ord_no, 
    	item, 
    	status, 
    	start_date, 
    	qty_oh, 
    	qty_r, 
    	qty_oh - qty_r as new_qoh
    FROM #tbl_order_age
    WHERE #tbl_order_age.ord_no = @ord_no
    
    
    DECLARE @RowCount BIGINT
    SET @RowCount = 1
    
    WHILE @RowCount > 0
    BEGIN
    	SET @ord_no = @ord_no + 1
    
    	INSERT INTO #CTE
    	Select T1.ord_no, 
    		T1.item, 
    		T1.status, 
    		T1.start_date, 
    		T1.qty_oh, 
    		T1.qty_r, 
    		T2.new_qoh - T1.qty_r as new_qoh
    	FROM #tbl_order_age as T1
    		INNER JOIN #CTE as T2 ON
    			T1.item = T2.item AND
    			T1.ord_no = @ord_no AND
    			T2.ord_no = @ord_no - 1
    
    	SET @RowCount = @@RowCount
    END
    
    select * from #CTE
    
    UPDATE U
    SET U.new_qoh = #CTE.new_qoh
    from #tbl_order_age as U
    	INNER JOIN #CTE ON
    		U.item = #CTE.item AND
    		U.ord_no = #CTE.ord_no
    
    select * from #tbl_order_age
    I am still assuming that ord_no is a sequence that restarts from 1 for each new item and counts upwards till n, the number of records for that given item. The order by which to group the records of one item is : item, status, start_date.
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    31
    Upgrade is almost here, but I need to bridge that gap.

    I appreciate the response. I am stuck in a meeting with out the ability to test it, but I will try it later today/tonight and will respond back with the results.

    Thanks in advance.

  5. #5
    Join Date
    Mar 2004
    Posts
    31
    Didn't see this part yesterday, in your message "I am still assuming that ord_no is a sequence that restarts from 1 for each new item and counts upwards till n, the number of records for that given item. The order by which to group the records of one item is : item, status, start_date."

    That is not actually the case. The way that inventory would get decremented is in the order of item, status, start_date,ord_no

    ord_no is not a record number, it is a work order #, and thus can actually be present in the table again attached to other items.

    I tried the code this morning, and it did not perform the decrement as it should which is probably a result of my not explaining the above clearly from the start. So any tips on what I should adjust?

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Auw, why can't you make life easier for both of us and just go out and spend some $$$$ to buy a recent version of SQL Server? I'm sure Bill and Steve will agree with me.
    It all becomes really obvious to me now: You are not a team player!

    My first idea was the use of ROW_NUMBER() in the recursive solution, but I removed it after I noticed the values of it were the same as those for ord_no (and that was just a coincidence, you wrote later). But the retarded 2000 doesn't know ROW_NUMBER(), so it's not a solution.

    I hope someone on this list will come up with a smart and simple solution. Guys?? There is a beer in it for the submitter with the coolest hack.
    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
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OK, here it comes, ugly as hell, slow as syrup, but it works.
    Code:
    DROP TABLE #tbl_order_age
    CREATE TABLE #tbl_order_age(
    	ord_no	INT	NOT NULL, 
    	item	VARCHAR(10)	NOT NULL, 
    	status	CHAR(1)	NOT NULL, 
    	start_date DATE	NOT NULL,
    	qty_oh	INT	NOT NULL, 
    	qty_r	INT	NOT NULL, 
    	new_qoh	INT	NOT NULL
    )
    
    INSERT INTO #tbl_order_age VALUES 
    (1, 'Component1', 'U', '20110808', 1088, 522, 1088),	-- 566
    (2, 'Component1', 'U', '20110810', 1088, 480, 1088),	--  87
    (1, 'Component2', 'U', '20110808', 943, 527, 943),	-- 416
    (2, 'Component2', 'U', '20110810', 943, 485, 943)	-- -68
    
    DROP TABLE #CTE
    CREATE TABLE #CTE(
    	RowNum	INT,
    	order_	CHAR(100),
    	ord_no	INT	NOT NULL, 
    	item	VARCHAR(10)	NOT NULL, 
    	status	CHAR(1)	NOT NULL, 
    	start_date DATE	NOT NULL,
    	qty_oh	INT	NOT NULL, 
    	qty_r	INT	NOT NULL, 
    	new_qoh	INT	NOT NULL
    )
    
    INSERT INTO #CTE
    Select NULL, NULL, 
    	ord_no, 
    	item, 
    	status, 
    	start_date, 
    	qty_oh, 
    	qty_r, 
    	qty_oh - qty_r as new_qoh
    FROM #tbl_order_age
    
    
    --order by tbl_order_age.item, 
    --	tbl_order_age.status, 
    --	tbl_order_age.start_date,
    --	tbl_order_age.ord_no
    --
    -- calculate order_, as we will need it often. 
    UPDATE U
    SET order_ = CAST(U.item as CHAR(10)) + U.status + CONVERT(VARCHAR(10), U.start_date, 102) + RIGHT('          ' + CAST (U.ord_no as VARCHAR(10)), 10)
    FROM #CTE as U
    
    DECLARE @RowCount BIGINT
    DECLARE @RowNum BIGINT
    
    SET @RowNum = 1
    
    -- Poor man's ROW_NUMBER()
    
    -- RowNum = 1 for smallest order_ per item
    UPDATE U
    SET RowNum = @RowNum
    FROM #CTE as U
    WHERE RowNum IS NULL AND
    	U.order_ = (SELECT MIN(T2.order_)
    			FROM #CTE as T2
    			WHERE U.item = T2.item
    			)
    
    SET @RowCount = 1
    
    -- assign next RowNum
    WHILE @RowCount > 0
    BEGIN
    	SET @RowNum = @RowNum + 1
    
    	UPDATE U
    	SET RowNum = @RowNum
    	FROM #CTE as U
    	WHERE RowNum IS NULL AND
    		U.order_ = (SELECT MIN(T2.order_)
    				FROM #CTE as T2
    				WHERE U.item = T2.item AND
    					T2.RowNum IS NULL
    				)
    	SET @RowCount = @@RowCount
    END
    
    
    SELECT * from #CTE
    
    
    SET @RowNum = 1
    
    -- first record of a set of records with the same Item
    UPDATE U
    	SET U.new_qoh = U.qty_oh - U.qty_r
    FROM #CTE as U
    WHERE U.RowNum = @RowNum
    
    
    SET @RowCount = 1
    
    WHILE @RowCount > 0
    BEGIN
    	SET @RowNum = @RowNum + 1
    
    	UPDATE U
    		SET U.new_qoh = T2.new_qoh - U.qty_r
    	FROM #CTE as U
    		INNER JOIN #CTE as T2 ON
    			U.item = T2.item AND
    			U.RowNum = @RowNum AND
    			T2.RowNum = @RowNum - 1
    
    	SET @RowCount = @@RowCount
    END
    
    select * from #CTE
    
    UPDATE U
    SET U.new_qoh = #CTE.new_qoh
    from #tbl_order_age as U
    	INNER JOIN #CTE ON
    		U.item = #CTE.item AND
    		#CTE.order_ = CAST(U.item as CHAR(10)) + U.status + CONVERT(VARCHAR(10), U.start_date, 102) + RIGHT('          ' + CAST (U.ord_no as VARCHAR(10)), 10)
    
    select * from #tbl_order_age
    You owe me a six-pack of beers, Rex.
    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

Posting Permissions

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