Recursion in VB, but what at SQL 1

[jadams0173]

Using SQL 2000.

I'm converting (I hope) another DB from access 2000 to SQL server 2000. This DB currently has an update program written in VB6 that updates the tables from other sql and access DB's and runs from my windows task scheduler every 20 minutes. I want to move this to entirely to SQL server if I can to eliminate the dependency to my PC being powered on and logged in. I'm to the point where I do some recursive lookups in my VB6 program and really don't know how to go about converting this to SQL.

I have one table that I query that holds all of the BOM data to make an assembly. On several occasions we have a release part number that is no the same as the ship part number. It is structured in the table in layers. I need to drill down through all these layers to get to the ship level.

We would realease part number 12345a now I must find the ship part number.

I query the table to look for compent type of P. If I find a P for part number 12345a then I take that part number and query the table. I keep drilling down until I find no more P's.

Release PN 12345a and the ship PN would be 89482G
12345a--
|
45678d--
|
89482G

Table looks like
Component COM_TYP
45678d P
89482G P

I can post the VB code if one wants to see it. I hope this makes enough since to give me some guidance or pointers on maybe how I can do this in SQL.

 

[gmmastros]

If I find a P for part number 12345a then I take that part number and query the table.

I suppose you have a query for this. With the sample data you provided, how do you get from one row to the next?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey George,

I do have a query, but it's all Jet and VB6 currently using recursion.

Below is the entire function I wrote to drill down through the table. It's call from my main function which passes in the initial item and set's first time to TRUE. I capture each parent/child in an array to load into a PARENT_CHILD table later. I think I can do appends in sql instead of storing each in an arry until I'm done.

Function Get_All_Phantoms(Item As String, FirstTime As Boolean)
Dim sqlPhantoms         As String
Dim rsPhantoms          As ADODB.Recordset
Dim fComponent          As ADODB.Field
Dim fPT_Use             As ADODB.Field
Dim fCom_Type           As ADODB.Field
Dim ErrSource           As String


'On Error GoTo GETPHANTOMS_Err:

If Item = "" Then Exit Function

'This is the query to find all the items used on the BOM
'and search for all the Phantoms "COM_TYP = P" 
sqlPhantoms = "SELECT COMPONENT, COM_TYP, PT_USE, QUANTITY " _
    & "From dbo.V_BOM " _
    & "WHERE PARENT='" & Item & "'  AND ITEM_TYPE='N' AND QTY_TYP='I' AND BKFLSH_STK='' AND QUANTITY > 0 " _
    & "GROUP BY COMPONENT, COM_TYP, PT_USE, QUANTITY " _
    & "HAVING ((Not (dbo.V_BOM.COM_TYP)='R' And Not (dbo.V_BOM.COM_TYP)='X'))"
  
 
Set rsPhantoms = New ADODB.Recordset


If FirstTime Then
    X = 0
    'redim the arrays
    ReDim aryN(3, X)
    ReDim aryP(0)
End If

ErrSource = "1"
    With rsPhantoms
        .Open sqlPhantoms, cnn4thShift, adOpenForwardOnly, adLockReadOnly, adCmdText
        
        Set fComponent = .Fields(0)
        Set fCom_Type = .Fields(1)
        Set fPT_Use = .Fields(2)
            
        Do Until .EOF
        
            'if the COM_TYP is N put COMPONENT in the aryN
            'if the COM_TYP is P put COMPONENT in the aryP
            
            Select Case fCom_Type '.Fields(5)  'COM_TYP
            
                Case "N"
                
                    aryN(1, X) = g_ParentItem   'PARENT  '.Fields(0)
                
                    aryN(2, X) = fComponent '.Fields(3) 'COMPONENT
                    
                    aryN(3, X) = fPT_Use '.Fields(9) 'PT_USE
                    
                    X = X + 1
                    ReDim Preserve aryN(3, X)
                        
                Case "P"
                
                    ReDim Preserve aryP(UBound(aryP) + 1)
                
                    aryP(UBound(aryP)) = fComponent '.Fields(3) 'COMPONENT
                    'loads the parent and child into the array
                    aryParentChild(1, UBound(aryParentChild, 2)) = PhantomParent
                    aryParentChild(2, UBound(aryParentChild, 2)) = fComponent
                    'prepare the array for the next elements
                    ReDim Preserve aryParentChild(2, UBound(aryParentChild, 2) + 1)
                    
            End Select
        
            .MoveNext
        Loop
        
ErrSource = "2"
        
    End With
    
 'CLEAN UP FIELD OBJECTS
Set fPT_Use = Nothing
Set fComponent = Nothing
Set fCom_Type = Nothing

rsPhantoms.Close
Set rsPhantoms = Nothing

'SETTING THE VARIABLE KEEPS ERROR 10, THE ARRAY IS LOCKED, FROM OCCURING.
lngUbound = UBound(aryP) '+ 1

'now test to see if the ubound or aryp is >=0.  if so call this function again
If lngUbound = 0 And aryP(lngUbound) = "" Then
    lngUbound = lngUbound - 1
End If

     Do Until lngUbound < 0
     
        strNextItem = aryP(lngUbound)
        
        'remove what we are searching for
        Call RemoveElement
        Call Get_All_Phantoms(strNextItem, False)
        
    Loop
    
'Debug.Print UBound(aryN)
'Debug.Print UBound(aryP)

Exit Function


GETPHANTOMS_Exit:
    Set fPT_Use = Nothing
    Set fComponent = Nothing
    Set fCom_Type = Nothing
    Set rsPhantoms = Nothing

    Exit Function

 

[gmmastros]

With SQL2005, you can use CTE (Common Table Expressions) to implement recursion.

Since you are using SQL2000, I would probably recommend a user defined function to implement this instead.

Something like this:

Create function dbo.GetPartNumber(@StartingValue VarChar(20))
Returns VarChar(20)
As
Begin
  Declare @NextPartNumber VarChar(20)

  Set @NextPartNumber = @StartingValue

  While Exists(Select * From V_BOM Where Parent = @NextPartNumber And Com_Type = 'P')
    Select @NextPartNumber = Component
    From   V_Bom
    Where  Parent = @NextPartNumber
           And COM_Type = 'P'

    Return @NextPartNumber
End

You could call it like this:

[tt][blue]Select dbo.GetPartNumber('12345a') As PartNumber[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey George! I'll give it a try as soon as I can. Just looking at the code, I can't say that I total understand it. Is the WHILE EXISTS a loop? As long as it finds P it will keep looping until it get's to the bottom. Then it will return @NextPartNumber which should be the last or ship level part number?

 

[gmmastros]

Yes. You appear to understand it correctly.

Each time through the loop, it re-assigns @NextPartNumber based on component. I added the "And Com_Type = 'P'" part because I *think* that is a condition that you need to check for based on your original question. You can, of course, add additional requirements (if you need to).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

George,

I finally got back to looking at this and it works for the part number that I tested it on. The only potential problem I see is that it is slow (24 seconds for 1 PN). I will have to do between 75 and 125 each time I run the SP.

 

[gmmastros]

First thing to try is.... check your indexes. Indexes can make a huge difference in performance.

Since you are using sql2000....

Open Query Analyzer
Load your query
On the top menu, click Query -> Analyze Query (or something like that)

It may be, "Database Tuning Advisor", but I can't really remember.

Basically, you can get Query Analyzer to look at the query and all the existing indexes and it may suggest additional indexes you can add to speed up the query.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Unless I did something wrong, the index tuner did not recommend a new index. I should have mentioned that dbo.V_BOM is on a linked server.

 

[gmmastros]

Can you create this function on the linked server and call it from there?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

I can try to have them do it for me, but I don't know if they will.

The more I ponder this, the more I don't think I went the right direction in trying to take the exact VB/JET code and move it SQL. Problem is, I'm not real sure how to ask the question, but I know what results I'd like to have.

I my *new* way of thinking I may have made it more difficult but it's going to take 3 steps from my old way into one.

I have a table of all jobs ready to release. I need to join the tblReadyToRelease with V_BOM where tblReadyToRelease.Parent = V_BOM.Parent(this could be a temp table?) AND ITEM_TYPE='N' AND QTY_TYP='I' AND BKFLSH_STK='' AND QUANTITY > 0
HAVING ((Not (dbo.V_BOM.COM_TYP)='R' And Not (dbo.V_BOM.COM_TYP)='X'))


The only catch is with the COM_TYP of 'P'. Under each 'P' there could be an entire new stucture of components including more P's.

OriginalParent = 12345
Component COM_TYP
A4567 N
A4532 N
A23415 N
98765 P
65325 P

NewParent 98765
Component COM_TYP
93838 N
20293 N
38472 P

If you capture all the N's in a table and then take each P item and process it for all the N's. Keep in mind that under each P there can be one or more other P's. You would always retain the original parent(12345) with each N item under each P to store in the table.

I need to drill down through each parent and find all the components. IF the COM_TYP is 'P' then I need to drill down through it for all components and if I find another 'P' COM_TYP repeat the process until I have found all the components for the original parent whose COM_TYP is 'N'.


They would be stored in a table with this structure:
Parent Component QTY PT_USE PT_USE_TYPE

 

[TLeaders]

You might whant to look at

http://www.tek-tips.com/viewthread.cfm?qid=1234949

This will explode an entire bom and return all the component items.

Good luck
Tom

 

[jadams0173]

I'll have a look at it TLeaders. Thanks for the post. I'm currently working on a solution using <in a whisper> cursors! YIKES

 

[gmmastros]

<in a whisper>

I HEARD THAT!



Funny thing is.... the advice I gave, and the code the TLeaders is suggesting both use a while loop. The problem people have with cursors is their performance. What most people don't say is.... "While loops and cursors are equally bad".

I think it's funny when someone says, "I can't use a cursor in my query because company policy prohibits them." Then, someone suggests a while loop, and that's a perfectly acceptable solution.

There are times when a cursor/while loop is a perfectly acceptable solution, just not many.

[small]You can stop whispering now[/small]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TLeaders]

Run Away Run Away

Cursors are bad enough, but over Linked Servers ....

You are moving from Gook Luck to God Help You.

 

[jadams0173]

George and TLeaders you both have a great since of humor ;-)

OK I'm through whispering now . From reading most posts on here I know that cursors are not the greatest in the world to use. In fact this is the first time I've ever tried to use one. What I hope to do is get this solution to work and post it. Maybe then someone will be gracious enough to help me come up with a non cursor solution.

 

[jadams0173]

...AND here it is. This does what I need but it is soo sloooow across a linked server. Good part is I will be able to put the SP on the linked server and call it from there. Only problem I can't seem to get right is I'm trying to maintain the original part number using and currently that's not working. I get the new item each time through.

It currently takes 22 minutes to run!!!

alter      procedure dbo.usp_SelectBOMComponents_JA @ITEM char(15), @BOMLevel int, @FirstTime Bit
										 
as

set nocount on

declare @PartNumber varchar(15),
        @Parent        varchar(15),
        @COM_TYP    char(1),
	   @Quantity   int,
        @PT_USE     varchar(10),
	   @OriginalParent char(15)

set @BOMLevel = @BOMLevel + 1

If @FirstTime = 1
	Set @OriginalParent = @Item

declare c1 cursor LOCAL FORWARD_ONLY for 
	select COMPONENT,
	       Parent,
	      COM_TYP,
		 QUANTITY,
		 PT_USE
	  from [CLTARSQL201\PROD04].FSXTRACT_PROD.dbo.V_BOM 
	 where PARENT = @Item 
	   and isnull(COMPONENT,'') <> ''
       and COM_TYP in ('N','P') 
	   AND QTY_TYP = 'I'

   FOR READ ONLY
open c1

while (1=1)
begin
	fetch next from c1 into @PartNumber, @Parent, @COM_TYP, @Quantity, @PT_USE

	if(@@FETCH_STATUS <> 0)
		break

	insert TEST([Level],Component,Parent,Com_Typ,Quantity,PT_USE) 
	values(@BOMLevel,@PartNumber,@OriginalParent,@Com_TYP,@Quantity,@PT_USE)

	if   @COM_TYP = 'P'--and@MB = 'M'
	begin
		set @FirstTime = 0
		exec usp_SelectBOMComponents @PartNumber, @BOMLevel
	end
end 
close c1
deallocate c1