Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 11th, 2005, 06:47 PM
Authorized User
 
Join Date: Apr 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default Nested Fetch statement

I'm trying to use a nested fetch statement to get the most current records for a record in a previous fetch and to get the last record in one Fetch within the nested loop.

Anyone got some expertise on this?

here's how it goes:

 DECLARE ALLTanks_Cur CURSOR FOR
 SELECT DISTINCT tk.vslname, tk.siteid, vf.id, vf.tankname,

 ORder by tk.vslname, tk.siteid, vf.id, vf.tankname,
 vf.tanknumber,vf.tanklocation

 OPEN ALLTanks_Cur -- all tanks for vessel
 FETCH NEXT FROM ALLTanks_Cur
 INTO “variables”

 --check the @@Fetch_status to see if there are any more tanks in the ----vessel to
 ---fetch
 while @@fetch_status = 0
 begin --fetch tank loop begin

 --Declare the variables to store the values in the Last_Tank_Insp ------Corsor FETCH used to find the next cellface for the full tank

     DECLARE Last_FULLTank_Insp_Cur SCROLL CURSOR FOR
     SELECT Distinct

     OPEN Last_FULLTank_Insp_Cur -- all tanks for vessel
     FETCH LAST FROM Last_FULLTank_Insp_Cur
     INTO “variables”

     WHILE @@FETCH_STATUS=0
     BEGIN
     --CLOSE Last_FULLTank_Insp_Cur
     --DEALLOCATE Last_FULLTank_Insp_Cur
 ----------------------------------------------------------------------

 --open full_cursor and insert records to temp variables

     declare full_cur CURSOR FOR -- (37 fields)

     open full_cur
     fetch next from full_cur into --(37 fields)
                     @VslName,

     Insert Into #vessel
         VALUES (@VslName,………) DECLARE Part_Tank_Insp_cur CURSOR FOR --(37 fields)

         OPEN Part_Tank_Insp_cur
         FETCH NEXT FROM Part_Tank_Insp_Cur
                 INTO @LPTTankName,

                 UPDATE #Vessel
                 SET CoatingsGen = @CoatingsGen,


                 WHILE @@FETCH_STATUS = 0
                 BEGIN

         FETCH NEXT FROM Part_Tank_Insp_Cur
                         INTO "Variables",

                     End
                     CLOSE Part_Tank_Insp_Cur
                     DEALLOCATE Part_Tank_Insp_Cur

                     begin


         --fetch next fulltank & cellfaces cursor
         fetch next from full_cur into
                     @VslName,…)


             END -- loop end
             CLOSE full_cur
             DEALLOCATE full_cur

         FETCH LAST FROM Last_FULLTank_Insp_Cur
         INTO “Variables”

       END -- last full tank loop
       CLOSE Last_FULLTank_Insp_Cur
       DEALLOCATE Last_FULLTank_Insp_Cur

     FETCH NEXT FROM ALLTanks_Cur
     INTO @LFTTankName, @LFTSiteID, @LFTTankID, @LFTTankName,
 @LFTTanknumber, @LFTTankLocation

 END -- all tanks loop
 CLOSE ALLTanks_Cur
 DEALLOCATE ALLTanks_Cur









Similar Threads
Thread Thread Starter Forum Replies Last Post
how to write sql statement for nested subuery thas123 SQL Server 2000 3 February 23rd, 2006 02:37 PM
Nested If Statement KCTin Visual Basic 2005 Basics 3 December 28th, 2005 01:53 PM
Nested IIF Statement Help Needed Corey Access 4 November 17th, 2005 10:19 AM
how to fetch records from website avats ADO.NET 0 November 3rd, 2005 03:45 PM
fetch in background? RCC_msolomon ADO.NET 6 June 25th, 2003 10:39 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.