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
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
Select * FROM Ttemp
Total | Limit_Desc
------
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 :p
If I havn't provided enough information then let me know please :)
-GeorgeVAre you doing this for fun or because you think you need to? Coz a cursor does not look like the ideal mechanism here.|||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 :)|||What is the value of @.@.FETCH_STATUS before the first fetch?
I usually fetch before checking that value, like so:
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|||Are you able to create a view\ stored procedure in sql server and call that from fox pro?|||Also - your problem is the bit you commented out. Why you do that George? Why? :)|||Oops - you also need to jiggle a couple of lines:
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|||Original
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!)
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* :)|||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?|||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" :p
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! :D|||If you can think of a better way then please feel free to pass opinion :)Two cursor-less alternatives:
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|||The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
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|||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 :D
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 :D|||... 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.
:)|||I'll definately come back with an answer on how I solved the overall problem :DHeh 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 ;)|||Always open to advice :)
I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
Thanks for everything Poots :beer:|||I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
My last tuppeneth I promise ;)must... try ... to ...resist...
it's no good - what happens next month when all your counts are out?|||Ah sorry, meant to clarify that.
This little fella will run before you run the INSERT INTO
DELETE FROM <mytable>|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.No - trouble shoot the view. The cursor is terrible. The delete\ insert is not much cop either.
Volatile data like this should always be derived at run time rather than stored if at all possible.
What do you mean by " the view is not picked up by the system and cannot be created the same by the system either..."?|||the system can only see views that it creates using it's in built data dictionary. Anything I produce in QA or EM simply canot be seen by the system - you cannot query it or even look at it.
The view cannot be built in the system because it exceeds the maximum characters (no matter how much I trim it down)... WHY IS THERE A LIMIT?!?
I'm afraid to say that I'm fed up of this problem today, so I'm going to get on with something else. The problem was solved hours and hours ago in QA, but porting this into our system is an absolute bitch.|||Ah - I think I see.
A third party system?
What is the char limit? Is it a limit on the SQL within the view? Can you not just insert info into one of application tables (*note - please don't try this - just a question)?|||3rd Party... Limit on the SQL... Don't understand :p|||I've effing done it!
Created a view using the software and then altered the view in QA.
Basically tricking it into doing what I wanted it to do :)
Damn that feels good.
(EDIT: want to know the best part? I didn't have to use any FoxPro! :D:D:D:D:D)
Now... to get exclusive access so I can put this live...
Thanks to everybody's 2 cents (and Poots's $2)!
I've learned a lot and finally cracked it (technically, I guess by "hacking" the program, but still!)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment