asked on

SQL Server 2008 FETCH with NESTED FETCH

I am attaching some code in SQL that uses FETCH. I am brand new at this. I tried to use FETCH in a similar way I used rs!Movenext in VBA, and I know it is not the same, but am not sure what to do. I used a nested FETCH, which I am pretty sure is wrong. I am open to another way of doing this.

I am basically trying to say, "Go through this table (child) where LnNum = LnNum (from the Master) and tell me this (could be anything????) I want it to loop through the child table based on the link to the master table. When the child gets to EOF, then Master moves to next, and we go through the child table again based on the new value in the master.

I am sure you will have questions. I am not sure how else to explain at this point.

Thanks - Hubbs

USE emdb_Reporting
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @Serv_LnNum varchar(20), @Guarantor_Last varchar(50)
DECLARE rsloans_cursor CURSOR FOR
SELECT     Serv_LnNum, Guarantor_Last
FROM       dbo.LnNum_Xref
WHERE     (NOT (Sale_Status LIKE 'SOLD%'))
ORDER BY Serv_LnNum

OPEN rsloans_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM rsloans_cursor
INTO @Serv_LnNum, @Guarantor_Last

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @VCC_LnNum varchar(20), @Guarantor_Last2 varchar(50), @STATUS varchar(20), @ASOFDT varchar(20)
	DECLARE rspmnt_cursor CURSOR FOR
	
	SELECT     dbo.LnNum_Xref.VCC_LnNum, dbo.LnNum_Xref.Guarantor_Last, dbo.tblDailyPmntStatus.STATUS, dbo.tblDailyPmntStatus.As_Of_Dt
	FROM       dbo.LnNum_Xref INNER JOIN
                      dbo.tblDailyPmntStatus ON dbo.LnNum_Xref.Serv_LnNum = @Serv_LnNum

	OPEN rspmnt_cursor
	
	FETCH NEXT FROM rspmnt_cursor
	INTO @VCC_LnNum, @Guarantor_Last2, @STATUS, @ASOFDT
	
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		-- Concatenate and display the current values in the variables.
		PRINT 'Borrower: ' + @VCC_LnNum + ' ' + @Guarantor_Last2 + ' ' + @STATUS + ' ' + @ASOFDT	
		FETCH NEXT FROM rspmnt_cursor
		INTO @VCC_LnNum, @Guarantor_Last2, @STATUS, @ASOFDT
	END
	
	CLOSE rspmnt_cursor
	DEALLOCATE rspmnt_cursor

FETCH NEXT FROM rsloans_cursor
INTO @Serv_LnNum, @Guarantor_Last

END

CLOSE rsloans_cursor
DEALLOCATE rsloans_cursor

GO

Open in new window

cyberkiwi

The code looks correct to me, the FetchStatus is tested independently and is correctly nested.
Were you after another way to write it or does it not work?

Hubbsjp21

ASKER

cyberkiwi,

Thanks for getting back to me. It is working, kinda. The first two fields in the Print Statement are always returning the same value for every row (the first record in the Master table), but the last two fields are changing (from the Child table) based on the link to the Master table. Would it help if I pasted enough results for you to see what I mean?(when I get to work tomorrow)

Thanks - Hubbs

Jim P.

Maybe you want to declare a table variable/temp table on the outer fetch, then do a left/right/inner join from the temptable to the real table as a select to the subtable. You have only one cursor, which you crush quickly and then the display is quick and easy as the final select.

Guy Hengel [angelIII / a3]

well, the question arises: what are you doing inside the loops, so that you really need the "cursor" looping?
you might be able to solve this more efficiently ...

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hubbsjp21

ASKER

Cyberkiwi - I THINK I understand what you are saying, and will attempt to do what you suggested.

Angel - I agree efficient is better. I have attached the VBA code I am using currently to accomplish the tasks. I am in the process of learning SQL, and am trying to convert my tasks from VBA in Access 2003, to using to Transact-SQL in SQL Server 2008.

1) In the first inner loop, I am simply getting the first record where Status = REO for each "WHERE VCC_LnNum ='" & rsLoans("VCC_LnNum") & "'". This, I admit could be done with MIN.

2) The second inner loop get the first record where Status = FC "WHERE VCC_LnNum ='" & rsLoans("VCC_LnNum") & "'". Again, I admit could be done with MIN.

3) The third loop however retrieves the most recent record where status = FC, but does not change to certain status' after that. So for example, if status becomes FC on 3/1/2010, and remains FC after that, or changes to REO or Closed after that, then 3/1/10 is retrieved. However, if status changes to something else besides FC after 3/1/10, it resets to NULL, unless it finds a new record where Status = FC.

The reasons I included the first two loops with the last loop was it allowed me to get all the needed data in one fell swoop and insert it into a table. However, I realize that this may not be the best way to do it in SQL. It retrieve the First REO date, the first FC date, and the most recent "new" fc date in one run for each record in "rs.loans". Hope this all makes sense.

Thanks for your help - Hubbs

Dim rsLoans As DAO.Recordset
Dim rsHistory As DAO.Recordset, rsHistAsc As DAO.Recordset
Dim rsString As DAO.Recordset
Dim sSql As String, sSql2 As String
Dim statusStr As String, FC1 As String, FC2 As String
Dim REOdt As Variant, FC1dt As Variant, FC2dt As Variant



sSql2 = "SELECT dbo_LnNum_Xref.VCC_LnNum, dbo_LnNum_Xref.Guarantor_Last "
sSql2 = sSql2 & "FROM dbo_LnNum_Xref LEFT JOIN tblREO_Dates ON dbo_LnNum_Xref.VCC_LnNum = tblREO_Dates.VCC_LnNum "
sSql2 = sSql2 & "WHERE (((dbo_LnNum_Xref.Sale_Status) Not Like 'Sold*') AND ((tblREO_Dates.VCC_LnNum) Is Null)) "
sSql2 = sSql2 & "ORDER BY dbo_LnNum_Xref.VCC_LnNum;"


Set rsLoans = CurrentDb.OpenRecordset(sSql2)
Set rsString = CurrentDb.OpenRecordset("tblREO_Dates")

rsLoans.MoveFirst

Do While Not rsLoans.EOF
      
   sSql = "SELECT dbo_LnNum_Xref.VCC_LnNum, dbo_LnNum_Xref.Guarantor_Last, dbo_tblDailyPmntStatus.As_Of_Dt, "
   sSql = sSql & "dbo_tblDailyPmntStatus.STATUS FROM dbo_LnNum_Xref LEFT JOIN dbo_tblDailyPmntStatus "
   sSql = sSql & "ON dbo_LnNum_Xref.Serv_LnNum = dbo_tblDailyPmntStatus.LOAN_NUM "
   sSql = sSql & "WHERE VCC_LnNum ='" & rsLoans("VCC_LnNum") & "'"
   sSql = sSql & " ORDER BY dbo_LnNum_Xref.VCC_LnNum, dbo_tblDailyPmntStatus.As_Of_Dt;"
   
   Set rsHistory = CurrentDb.OpenRecordset(sSql)
   
   rsHistory.MoveFirst
      
   REOdt = Null
   statusStr = ""
   FC1dt = Null
   FC1Str = ""
   FC2dt = Null
   FC2Str = ""
   
   
      Do While Not rsHistory.EOF
      
        If statusStr = "REO" Then
        rsHistory.MoveLast
        ElseIf rsHistory!Status Like "REO" Then
        statusStr = rsHistory!Status
        REOdt = rsHistory!As_Of_Dt
        Else
        statusStr = ""
        End If
               
        rsHistory.MoveNext
        
      Loop
      
      rsHistory.MoveFirst
      Do While Not rsHistory.EOF
      
        If rsHistory!Status Like "FC" And FC1Str = "" And IsNull(FC1dt) Then
        FC1Str = rsHistory!Status
        FC1dt = rsHistory!As_Of_Dt
        rsHistory.MoveLast
        End If
               
        rsHistory.MoveNext
        
      Loop
      
      rsHistory.MoveFirst
      Do While Not rsHistory.EOF
      
        If rsHistory!Status Like "FC" And FC2Str = "" And IsNull(FC2dt) Then
        FC2dt = rsHistory!As_Of_Dt
        FC2Str = rsHistory!Status
        ElseIf rsHistory!Status Like "REO" Or rsHistory!Status Like "Closed" Then
        rsHistory.MoveLast
        ElseIf rsHistory!Status Like "CURRENT" Or rsHistory!Status Like "30 DPD" Or rsHistory!Status Like "60 DPD" _
        Or rsHistory!Status Like "90 DPD" Or rsHistory!Status Like "REPERF" Then
        FC2Str = ""
        FC2dt = Null
        End If
               
        rsHistory.MoveNext
        
      Loop
      
      With rsString
      
      If Not IsNull(REOdt) Or Not IsNull(FC1dt) Or Not IsNull(FC2dt) Then
      .AddNew
      !VCC_LnNum = rsLoans!VCC_LnNum
      !Guarantor_Last = rsLoans!Guarantor_Last
      
      If Not IsNull(REOdt) Then
      !REO_DT = DateValue(REOdt)
      Else
      !REO_DT = Null
      End If
      
      If Not IsNull(FC1dt) Then
      !FC_FIRST = DateValue(FC1dt)
      Else
      !FC_FIRST = Null
      End If
      
      If Not IsNull(FC2dt) Then
      !FC_CURR = DateValue(FC2dt)
      Else
      !FC_CURR = Null
      End If
      
      .Update
      End If
      
     
      End With
      
 rsLoans.MoveNext

 Loop

Open in new window

Hubbsjp21

ASKER

Jimpen,

If I were to try your suggestion, for which I can sort of see the code in my head, would I be able to accomplish the tasks I outlined above?

Hubbs

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hubbsjp21

ASKER

Wow - I really appreciate all the help y'all are giving me. A lot to digest. I am going to be working on it throughout the day.

Mark - Let me just answer your first question for now: Yes, there is only one unique row per VCC_LnNum in LnNum_Xref . The only thing the "recorset" did was filter it down to less records to step through.

Hubbs

Hubbsjp21

ASKER

Cyberkiwi,

I understand what you are saying, but I am a little perplexed as to how I incorporate what you gave me back. what are x1 and x2? That code goes somewhere in the inner fetch, right? Sorry so dense!

Thanks - Hubbs

Hubbsjp21

ASKER

When I asked the q, I did not ask for help on the task inside the fetch, so I consider Mark's answer complete. However, I see that the loop is inefficient, and will ask a new q based on Mark's qs to me at the bottom. Please look for a q entitled "A better alternative to this FETCH loop". Gave Kiwi 100 points for effort. Thanks to all

Mark Wills

Hi Hubbsjp21,

So very happy to have been able to help you with your issue, and welcome to the wonderful world of T-SQL programming :)

When you go to ask your new question, you can use the "Ask a related Question" button up the top, and all the experts on this question will be notified of your new question when you post it.

Hubbsjp21

ASKER

Got it. Thanks.