Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Smile Unanswered: Cursor syntax / help :)

    Hey lads and lasses, I have been playing around with cursors and I just cant get my code to do what I tell it

    Below is the code that I have written so far, which is trying to do something like this -
    1) Create table - tServiceCategory
    2) Create table - tTemp
    3) Populate tServiceCategory
    4) Declare variables and cursor
    5) Run SQL I want to cursor through
    6) Open cursor
    7) While @@FETCH_STATUS = 0 insert the values obtained by the cursor into tTemp
    8) Close and deallocate cursor
    9) Drop tables
    Code:
    CREATE TABLE tServiceCategory
    (
    Lower_Limit		numeric,
    Upper_Limit		numeric,
    Sort_Order		numeric
    )
    
    CREATE TABLE tTemp
    (
    Total			numeric,
    Limit_Desc		varchar(16)
    )
    
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
    
    DECLARE @Total numeric
    DECLARE @Limit_Description varchar(16)
    DECLARE MyCursor CURSOR FOR
    
    	SELECT	 Count(*) AS 'Total'
    		,CAST(Lower_Limit AS VARCHAR) +
    			(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
    				THEN '+'
    				ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
    			END) + ' months'
    		AS Limit_Description
    	FROM	Employee e
    	INNER JOIN tServiceCategory s
    		ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
    	GROUP
    	BY	 s.Sort_Order, s.Lower_Limit, s.Upper_Limit
    	ORDER
    	BY	s.Sort_Order
    
    OPEN MyCursor
    
    --	FETCH NEXT FROM MyCursor
    --	INTO @Total, @Limit_Description
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	FETCH NEXT FROM MyCursor
    	INTO @Total, @Limit_Description
    	INSERT INTO tTemp(Total) VALUES (@Total)
    END
    
    	SELECT * FROM tTemp
    	
    	SELECT	 Count(*) AS 'Total'
    		,CAST(Lower_Limit AS VARCHAR) +
    			(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
    				THEN '+'
    				ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
    			END) + ' months'
    		AS Limit_Description
    	FROM	Employee e
    	INNER JOIN tServiceCategory s
    		ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
    	GROUP
    	BY	 s.Sort_Order, s.Lower_Limit, s.Upper_Limit
    	ORDER
    	BY	s.Sort_Order
    
    CLOSE	   MyCursor
    DEALLOCATE MyCursor
    DROP TABLE tTemp
    DROP TABLE tServiceCategory
    Results
    Code:
    Select * FROM Ttemp
    
    Total | Limit_Desc
    ------------------
    Code:
    Total | Limit_Desc
    ------------------
    227	0 to 6 months
    448	7 to 12 months
    573	13 to 24 months
    910	25 to 60 months
    911	61 to 120 months
    614	121 to 240 months
    250	241+ months
    So it appears that tTemp is not getting populated... But I'm banging my head against the wall with these cursors, and I can't work out what I'm doing wrong

    If I havn't provided enough information then let me know please

    -GeorgeV
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you doing this for fun or because you think you need to? Coz a cursor does not look like the ideal mechanism here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Beause I've been asked to make this a query in FoxPro so that our users can run it... Except the system doesn't support the kind of join in a regular query, so I'm making it a process. I am trying to put the results of the SQL statement into a temporary table so I can report on it... Complicated to explain, but this was the solution I came up with!

    If you can think of a better way then please feel free to pass opinion
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    What is the value of @@FETCH_STATUS before the first fetch?

    I usually fetch before checking that value, like so:
    Code:
    FETCH NEXT FROM MyCursor
    INTO @Total, @Limit_Description
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	INSERT INTO tTemp(Total) VALUES (@Total)
    
    	FETCH NEXT FROM MyCursor
    	INTO @Total, @Limit_Description
    END

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you able to create a view\ stored procedure in sql server and call that from fox pro?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - your problem is the bit you commented out. Why you do that George? Why?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - you also need to jiggle a couple of lines:

    Code:
    CREATE TABLE tServiceCategory
    (
    Lower_Limit  numeric,
    Upper_Limit  numeric,
    Sort_Order  numeric
    )
    CREATE TABLE tTemp
    (
    Total   numeric,
    Limit_Desc  varchar(16)
    )
    CREATE TABLE Employee
    (
    continuous_start_date   SMALLDATETIME
    )
    INSERT INTO employee
    SELECT '20050101'
    UNION ALL
    SELECT '20060101'
    UNION ALL
    SELECT '20070101'
    UNION ALL
    SELECT '20070202'
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
    DECLARE @Total numeric
    DECLARE @Limit_Description varchar(16)
    DECLARE MyCursor CURSOR FOR
     SELECT  Count(*) AS 'Total'
      ,CAST(Lower_Limit AS VARCHAR) +
       (CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
        THEN '+'
        ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
       END) + ' months'
      AS Limit_Description
     FROM Employee e
     INNER JOIN tServiceCategory s
      ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
     GROUP
     BY  s.Sort_Order, s.Lower_Limit, s.Upper_Limit
     ORDER
     BY s.Sort_Order
    OPEN MyCursor
     FETCH NEXT FROM MyCursor
     INTO @Total, @Limit_Description
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
     INSERT INTO tTemp(Total) VALUES (@Total)
     FETCH NEXT FROM MyCursor
     INTO @Total, @Limit_Description
    
    END
     SELECT * FROM tTemp
     
     SELECT  Count(*) AS 'Total'
      ,CAST(Lower_Limit AS VARCHAR) +
       (CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
        THEN '+'
        ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
       END) + ' months'
      AS Limit_Description
     FROM Employee e
     INNER JOIN tServiceCategory s
      ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
     GROUP
     BY  s.Sort_Order, s.Lower_Limit, s.Upper_Limit
     ORDER
     BY s.Sort_Order
    CLOSE    MyCursor
    DEALLOCATE MyCursor
    DROP TABLE tTemp
    DROP TABLE tServiceCategory
    DROP TABLE Employee
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Original
    Code:
    OPEN MyCursor
    
    --	FETCH NEXT FROM MyCursor
    --	INTO @Total, @Limit_Description
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	FETCH NEXT FROM MyCursor
    	INTO @Total, @Limit_Description
    	INSERT INTO tTemp(Total) VALUES (@Total)
    END
    With changes (they seem so obvious now!)
    Code:
    OPEN MyCursor
    
    	FETCH NEXT FROM MyCursor
    	INTO @Total, @Limit_Description
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	INSERT INTO tTemp(Total) VALUES (@Total)
    	FETCH NEXT FROM MyCursor
    	INTO @Total, @Limit_Description
    END
    Thank you for your help guys, much appreciated. My first attempt at a cursor wasn't so bad

    *gives Poots and ivon gold stars*
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    My first attempt at a cursor wasn't so bad
    Apart from the fact that a cursor appears totally inappropriate for what you are doing have you not had the "OMG - don't write cursors unless you are certain there is no other way to do it" speil yet?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    OOh, nobody has given me that talk so far!
    The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk"

    So what would be my other options? I'm more than willing to re-evaluate my approach... But coding in our system sucks, I don't need a cursor or anything if I didn't have to make it available to users... I hate users!
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    If you can think of a better way then please feel free to pass opinion
    Two cursor-less alternatives:
    Code:
     
    CREATE TABLE tServiceCategory
    (
    Lower_Limit numeric,
    Upper_Limit numeric,
    Sort_Order numeric
    )
    CREATE TABLE tTemp
    (
    Total numeric,
    Limit_Desc varchar(16)
    )
    CREATE TABLE Employee
    (
    continuous_start_date SMALLDATETIME
    )
    INSERT INTO employee
    SELECT '20050101'
    UNION ALL
    SELECT '20060101'
    UNION ALL
    SELECT '20070101'
    UNION ALL
    SELECT '20070202'
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
    INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
    GO
     
     
    --Alternative #1 - use a view
    CREATE VIEW myview
    AS
    SELECT Count(*) AS 'Total'
    ,CAST(Lower_Limit AS VARCHAR) +
    (CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
    THEN '+'
    ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
    END) + ' months'
    AS Limit_Description
    FROM Employee e
    INNER JOIN tServiceCategory s
    ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
    GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
    GO
     
    SELECT *
    FROM dbo.myview
     
    --Alternative #2 - if you simply MUST populate a temp table use SET BASED logic
    INSERT INTO tTemp (total, limit_desc)
    SELECT Count(*) AS 'Total'
    ,CAST(Lower_Limit AS VARCHAR) +
    (CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
    THEN '+'
    ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
    END) + ' months'
    AS Limit_Description
    FROM Employee e
    INNER JOIN tServiceCategory s
    ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
    GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
     
    SELECT *
    FROM dbo.ttemp
     
    DROP TABLE tTemp
    DROP TABLE tServiceCategory
    DROP TABLE Employee
    DROP VIEW myview
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk"
    Either he is doing a lot of specialised struff or he is not a SQL expert.

    SQL Server is rubbish at iterative stuff - SQL is a declarative, set based language. ALWAYS try to do everything set based where possible and only consider curosrs as a last resort. This is an area where a lot of procedural coders get tripped up when programming in SQL.

    Cursors are useful for:
    Admin DDL (building dynamic strings by looping through "collections" (for example looping through a table with database names and performing the same SQL action on each database)).
    Certain DML statements where "previous"\ "later" rows (logically speaking of course ) need to be prepared to the "current" row (e.g. culminative row counts). Even here though you are best off testing. I have some links to articles if you would like.

    I can't, off the top of my head, think of another time you would want to use a cursor.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    INSERT INTO! D'oh!
    Well, it was nice learning cursors a wee bit anyway
    Why didn't I think of that?
    Well, I guess it's high time to port this into FoxPro so I can play with it and see what damage I can cause

    The table isn't neccessarily temporary, as in, it might not be dropped depending on how I can get FoxPro to interact with temporary tables... We'll see I suppose!

    I'll definately come back with an answer on how I solved the overall problem
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    .... and finally. Do you remember me saying I am hopeless with cursor syntax? The reason is the above - I barely use them. In fact, I code up loops anyway so I never use them but the point is I do very little iterative stuff in SQL.

    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I'll definately come back with an answer on how I solved the overall problem
    Heh heh - if you want one last bit of advice - dump the temp table thing too and just use the view. My last tuppeneth I promise
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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